sql仅按顺序的行进行分组

Question

假设我有下表:

MyTable
---------
| 1 | A |
| 2 | A |
| 3 | A |
| 4 | B |
| 5 | B |
| 6 | B |
| 7 | A |
| 8 | A |
---------

我需要 sql 查询来输出以下内容:

---------
| 3 | A |
| 3 | B |
| 2 | A |
---------

基本上，我正在执行group by，但仅针对序列中在一起的行。有什么想法吗？

请注意，数据库位于 SQL Server 2008 上。有一篇关于此主题的文章，但它使用了 Oracle 的 lag() 函数。

Answer 1

这被称为“岛屿”问题。使用 Itzik Ben Gan 的方法:

;WITH YourTable AS
(
SELECT 1 AS N, 'A' AS C UNION ALL
SELECT 2 AS N, 'A' AS C UNION ALL
SELECT 3 AS N, 'A' AS C UNION ALL
SELECT 4 AS N, 'B' AS C UNION ALL
SELECT 5 AS N, 'B' AS C UNION ALL
SELECT 6 AS N, 'B' AS C UNION ALL
SELECT 7 AS N, 'A' AS C UNION ALL
SELECT 8 AS N, 'A' AS C
),
     T
     AS (SELECT N,
                C,
                DENSE_RANK() OVER (ORDER BY N) - 
                DENSE_RANK() OVER (PARTITION BY C ORDER BY N) AS Grp
         FROM   YourTable)
SELECT COUNT(*),
       C
FROM   T
GROUP  BY C,
          Grp 
ORDER BY MIN(N)