Generate all the continous intervals from a series



I have this problem I can't quite solve. I can get the missing numbers interval, but I can't piece them back together to from my continous series.

So if I have a series defined as [1000,1001,1002,1003,1005,1006,1008] I want to extract the three continuous series [1000,1001,1002,1003] and [1005,1006] and [1008]. Using a simple CTE I got 1003, 1005,1006 and 1008, so I'm able to get the end and start of the intervals, but what now?

In the end I want a table that looks like this:

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

Anyone got a smart solution they want to share?

EDIT: Here is the (probably reduntant) CTE:

WITH MissingNumbers (FromNumber, ToNumber) AS
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. there was a change of plans, so I no longer need this kind of solution. Thank you for all the replies though! Very helpful :D

3 ответов


Самый простой выход - создать таблицу с 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)


 SELECT SSTART.num series_start, MIN(SEND.num) series_end
FROM   #series SSTART, #series SEND
      /* 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 */
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