假设我有下表:
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() 函数。
这就是所谓的“岛屿”问题。使用 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)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)