那很有趣:)
;WITH cte as (SELECt Messages.Message, Timestamp, ROW_NUMBER() OVER(PARTITION BY Message ORDER BY Timestamp) AS gn,ROW_NUMBER() OVER (ORDER BY Timestamp) AS rnFROM Messages), cte2 AS (SELECt Message, Timestamp, gn, rn, gn - rn as gbFROM cte ), cte3 AS (SELECt Message, MIN(Timestamp) As Ts, COUNT(1) as CntFROM cte2GROUP BY Message, gb)SELECt Message, Cnt FROM cte3ORDER BY Ts
这是结果集：
Message Cnt A 2 B 1 A 3 B 1
该查询可能更短，但我以这种方式发布，因此您可以看到正在发生的事情。结果完全符合要求。这是最重要的部分，
gn -rn该想法是对每个分区中的行进行编号，同时对整个集中的行进行编号，然后，如果从另一个中减去一个，则将获得每个组的“排名”。
;WITH cte as (SELECt Messages.Message, Timestamp, ROW_NUMBER() OVER(PARTITION BY Message ORDER BY Timestamp) AS gn,ROW_NUMBER() OVER (ORDER BY Timestamp) AS rnFROM Messages), cte2 AS (SELECt Message, Timestamp, gn, rn, gn - rn as gbFROM cte )SELECt * FROM cte2Message Timestampgn rn gbA 2015-03-29 00:00:00.000 1 1 0A 2015-03-29 00:01:00.000 2 2 0B 2015-03-29 00:02:00.000 1 3 -2A 2015-03-29 00:03:00.000 3 4 -1A 2015-03-29 00:04:00.000 4 5 -1A 2015-03-29 00:05:00.000 5 6 -1B 2015-03-29 00:06:00.000 2 7 -5
