按值对行进行分组和计数，直到其更改为止

按值对行进行分组和计数，直到其更改为止

那很有趣:)

;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