生成一系列的所有连续间隔

Question

我有这个问题我无法完全解决。我可以得到丢失的数字间隔，但我无法将它们重新拼凑成我的连续系列。

因此，如果我有一个定义为 [1000,1001,1002,1003,1005,1006,1008] 的系列，我想提取三个连续系列 [1000,1001,1002,1003] 和 [1005,1006] 和 [1008] 。使用简单的 CTE，我得到了 1003、1005、1006 和 1008，因此我能够获得间隔的结束和开始，但现在怎么办？

最后，我想要一个如下所示的表格：

|to   |from  |  
|1000 |1003  |  
|1005 |1006  |  
|1008 |1008  |

有人有想要分享的智能解决方案吗？

编辑： 这是（可能是多余的）CTE：

WITH MissingNumbers (FromNumber, ToNumber) AS
(   
SELECT 
    T1.TaxLabelNumber, 
    T2.TaxLabelNumber
FROM TaxLabel T1
JOIN TaxLabel T2
    ON T1.TaxLabelId + 1 = T2.TaxLabelId
WHERE T1.TaxLabelNumber <> T2.TaxLabelNumber - 1
)
SELECT * INTO #TempNumbers 
FROM MissingNumbers 

EDIT2：Ofc。计划有变，所以我不再需要这种解决方案。不过还是谢谢大家的回复！非常有帮助：D

Answer 1

最简单的方法是使用一个包含 TaxLabelNumbers 的表，以便您可以进行外部联接。

也可以在 CTE 中创建此类表，但效率不高。

with TaxLabelSeq( Number ) as  
(  
    select @FromNumber as Number  
        union all  
    select Number + 1  
        from NumberSequence  
        where Number < @ToNumber
)

CTE 明智地默认为 100 次递归，因此如果您需要超过 100 个数字，则需要将其提高：

select * from TaxLabelSeq option (MaxRecursion 4711)

Answer 2

WITH    data AS
        (
        SELECT  1000 AS number
        UNION ALL
        SELECT  1001
        UNION ALL
        SELECT  1002
        UNION ALL
        SELECT  1003
        UNION ALL
        SELECT  1005
        UNION ALL
        SELECT  1006
        UNION ALL
        SELECT  1008
        ),
        rows AS
        (
        SELECT  q2.number AS nnumber, q.number AS number
        FROM    (
                SELECT  number
                FROM    data di
                WHERE   NOT EXISTS
                        (
                        SELECT  NULL
                        FROM    data dn
                        WHERE   dn.number = di.number - 1
                        )
                ) q
                OUTER APPLY
                (
                SELECT  TOP 1 number
                FROM    data dp
                WHERE   dp.number < q.number
                ORDER BY
                        dp.number DESC
                ) q2
        UNION ALL
        SELECT  TOP 1 number, NULL
        FROM    data
        ORDER BY
                number DESC
        ),
        rns AS
        (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY nnumber) AS rn
        FROM    rows
        )
SELECT  re.number, rb.nnumber
FROM    rns re
JOIN    rns rb
ON      rb.rn = re.rn + 1

Answer 3

试试这个

SELECT SSTART.num series_start, MIN(SEND.num) series_end
FROM   #series SSTART, #series SEND
WHERE
      /* anything that does not have a predecessor is a START */
      SSTART.num - 1 NOT IN (SELECT num FROM #series) AND
      /* anything that does not have a following entry is an END */
      SEND.num + 1 NOT IN (SELECT num FROM #series)   AND
      /* now join each START with every END above it */
      SEND.num >= SSTART.num
      /* we group over each START, so we can get the corresponding END with MIN */
GROUP BY SSTART.num