说我有下表：
MyTable --------- | 1 | A | | 2 | A | | 3 | A | | 4 | B | | 5 | B | | 6 | B | | 7 | A | | 8 | A | ---------
--------- | 3 | A | | 3 | B | | 2 | A | ---------
基本上我正在做一个组,但是只是为了排列在一起的行.有任何想法吗？
解决方法
这被称为“岛屿”问题.使用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)
