如何在sql查询中获取连续记录项的最大值？

Question

我知道如何在sql中使用MAX()函数和GROUP BY子句，但是，在我的示例中，我要分组的记录有点不同。

例如，我有一个下表

HostName   ModifyDate                Sr No
-------------------------------------------
PC-1       2015-12-04 08:45:52.847   1
PC-1       2015-12-04 08:48:51.025   2
PC-2       2015-12-04 08:50:01.125   3
PC-2       2015-12-04 08:50:45.545   4
PC-2       2015-12-04 08:53:44.897   5
PC-1       2015-12-04 09:02:17.524   6
PC-2       2015-12-04 09:18:36.788   7
PC-2       2015-12-04 09:22:01.041   8
PC-1       2015-12-04 09:31:41.744   9

结果应该是这样

HostName   ModifyDate                Sr No
------------------------------------------
PC-1       2015-12-04 08:48:51.025   2
PC-2       2015-12-04 08:53:44.897   5
PC-1       2015-12-04 09:02:17.524   6
PC-2       2015-12-04 09:22:01.041   8
PC-1       2015-12-04 09:31:41.744   9

Sql Fiddle

以上结果显示了连续HostName记录的最大日期记录。你可以看到有3个条目(SrNo 3,4,5)，但我只选了第5条，这是所有三个条目中最新的。在前两个记录(SrNo 1,2)中也使用了相同的场景，我只记录了第二条记录，这是这两条记录中的最新记录。

问题是我不能拿最大日期记录。如果我使用MAX(ModifyDate)与组HostName，那么它将只返回两个记录(Sr第8和9)，这是最新的。我想记录下每个连续的HostName条目的最大记录。

注意:我不能为它创建存储过程。我希望通过一个sql语句获得该记录。桌子上没有Sr，no字段。我拿它只是为了了解这个案子。。

Answer 1

这是一个毗连枣群岛分组问题，可以用ROW_NUMBER来解决。

SQL Fiddle

WITH Cte AS(
    SELECT *,
        grp     = ROW_NUMBER() OVER(ORDER BY ModifyDate) -
                  ROW_NUMBER() OVER(PARTITION BY HostName ORDER BY ModifyDate),
        SrNo    = ROW_NUMBER() OVER(ORDER BY ModifyDate)
    FROM LOG
),
CteFinal AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY HostName, grp ORDER BY SrNo DESC)
    FROM Cte
)
SELECT
    HostName, ModifyDate, SrNo
FROM CteFinal
WHERE rn = 1
ORDER BY SrNo

Answer 2

你可以试试这种方法..。

SQL Fiddle

WITH CTE1 AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT ModifyDate)) RN,* FROM DATES9
),
CTE2 AS
(
    SELECT CAST(1 AS INT) AS NRN, * FROM CTE1 WHERE RN=1
    UNION ALL
    SELECT CAST((CASE WHEN C1.HOSTNAME = C2.HOSTNAME THEN NRN ELSE NRN+1 END) AS INT) AS NRN,C1.*
    FROM CTE1 C1 INNER JOIN CTE2 C2 ON C2.RN = C1.RN-1
)
select  MAX(MODIFYDATE),HOSTNAME 
from CTE2
GROUP BY NRN,HOSTNAME
ORDER BY NRN