从表中选择连续范围

Question

我需要基于连续数字（第N列）和这些数字涉及的相同“类别”（下面的C列）从表中提取连续范围。图形上看起来像这样：

 N  C  D
--------
 1  x  a           C  N1  N2  D1  D2
 2  x  b          ------------------
 3  x  c           x   1   4   a   d     (continuous range with same N)
 4  x  d    ==>    x   6   7   e   f     (new range because "5" is missing)
 6  x  e           y   8  10   g   h     (new range because C changed to "y")
 7  x  f
 8  y  g
 9  y  h
10  y  i

SQL Server是2005。谢谢。

Answer 1

DECLARE @myTable Table
(
    N INT,
    C CHAR(1),
    D CHAR(1)
)
INSERT INTO @myTable(N,C,D) VALUES(1,  'x', 'a');
INSERT INTO @myTable(N,C,D) VALUES(2,  'x', 'b');
INSERT INTO @myTable(N,C,D) VALUES(3,  'x', 'c');
INSERT INTO @myTable(N,C,D) VALUES(4,  'x', 'd');
INSERT INTO @myTable(N,C,D) VALUES(6,  'x', 'e');
INSERT INTO @myTable(N,C,D) VALUES(7,  'x', 'f');
INSERT INTO @myTable(N,C,D) VALUES(8,  'y', 'g');
INSERT INTO @myTable(N,C,D) VALUES(9,  'y', 'h');
INSERT INTO @myTable(N,C,D) VALUES(10, 'y', 'i');


WITH StartingPoints AS(

    SELECT A.*, ROW_NUMBER() OVER(ORDER BY A.N) AS rownum
    FROM @myTable AS A
    WHERE NOT EXISTS(
        SELECT *
        FROM @myTable B
        WHERE B.C = A.C
          AND B.N = A.N - 1
    )
 ),
 EndingPoints AS(
    SELECT A.*, ROW_NUMBER() OVER(ORDER BY A.N) AS rownum
    FROM @myTable AS A
    WHERE NOT EXISTS (
        SELECT *
        FROM @myTable B
        WHERE B.C = A.C
          AND B.N = A.N + 1
    )
 ) 
SELECT StartingPoints.C,
       StartingPoints.N AS [N1],
       EndingPoints.N AS [N2],
       StartingPoints.D AS [D1],
       EndingPoints.D AS [D2] 
FROM StartingPoints
JOIN EndingPoints ON StartingPoints.rownum = EndingPoints.rownum

结果 ：

C    N1          N2          D1   D2
---- ----------- ----------- ---- ----
x    1           4           a    d
x    6           7           e    f
y    8           10          g    i