MS SQL 2012:查找编号规则

Question

>我有这样的表格：

    WITH S AS (
    SELECT 'B' type, 1 number
UNION SELECT 'B', 2
UNION SELECT 'B', 3
UNION SELECT 'B', 4
UNION SELECT 'B', 5
UNION SELECT 'A', 6
UNION SELECT 'A', 7
UNION SELECT 'B', 8
UNION SELECT 'B', 9
UNION SELECT 'B', 10
UNION SELECT 'C', 11
UNION SELECT 'A', 12
UNION SELECT 'B', 13
UNION SELECT 'B', 14
UNION SELECT 'B', 15
UNION SELECT 'B', 16
UNION SELECT 'B', 17
UNION SELECT 'A', 18
UNION SELECT 'C', 19
UNION SELECT 'B', 20
UNION SELECT 'B', 21
    ) 

如何获得每种类型连续(例如 3 个或更多(的独特数字序列？例如，对于 B 1~5，会有类似"B1"的东西，对于 B 8~10 - "B2"等。我怀疑应该有类似LEAD/LAG和DENSE_RANK()的组合，但不知道如何应用。数字是唯一的，如果这很重要的话。

结果应如下所示：

Type   Number  Sequence
-----------------------
B        1        B1
B        2        B1
B        3        B1
B        4        B1
B        5        B1
A        6        NULL
......................
B        8        B2
B        9        B2
B        10       B2
C        11       NULL
A        12       NULL
B        13       B3
....................
B        17       B3

Answer 1

一种方法是使用DENSE_RANK()和行号差异的组合。

WITH S AS (
    SELECT 'B' type, 1 number
UNION SELECT 'B', 2
UNION SELECT 'B', 3
UNION SELECT 'B', 4
UNION SELECT 'B', 5
UNION SELECT 'A', 6
UNION SELECT 'A', 7
UNION SELECT 'B', 8
UNION SELECT 'B', 9
UNION SELECT 'B', 10
UNION SELECT 'C', 11
UNION SELECT 'A', 12
UNION SELECT 'B', 13
UNION SELECT 'B', 14
UNION SELECT 'B', 15
UNION SELECT 'B', 16
UNION SELECT 'B', 17
UNION SELECT 'A', 18
UNION SELECT 'C', 19
UNION SELECT 'B', 20
UNION SELECT 'B', 21
    ) 
SELECT type, 
       number, 
       sequence = CASE 
            WHEN type = 'B' 
            THEN 'B' + CAST(DENSE_RANK() OVER (ORDER BY CASE WHEN type = 'B' THEN 0 ELSE 1 END, RN) AS VARCHAR(10)) 
            END
FROM (
    SELECT *, RN = ROW_NUMBER() OVER (ORDER BY number) - ROW_NUMBER() OVER (PARTITION BY type ORDER BY number)
    FROM S
    ) AS T
ORDER BY number;

Answer 2

有一个回复，只是几分钟，它包含了我想要的确切解决方案(其余的我可以弄清楚(：

SELECT type, R1 = min(number), R2 = max(number) FROM (
SELECT *, number - ROW_NUMBER() OVER (PARTITION BY type ORDER BY number) Grp From S
) A
GROUP BY type

无法评价答案 - 那时它已经被删除了。解决方案是找到"差距和岛屿"——现在我知道了。