说我有下表：
- 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)
