SQL Server 从列表中获取串联的数字范围(序列中的岛屿)

Question

我有以下表结构:

DECLARE @headerTable TABLE (HeaderTableId INT)
INSERT INTO @headerTable VALUES (1), (2), (3)

DECLARE @detailTable TABLE (HeaderTableIdFK INT, DetailTableNumber INT)
INSERT INTO @detailTable VALUES (1,1), (1,2), (1,3), (1,6), (1,7), (1,8)
                                ,(2,51), (2,52), (2,53),(2,100), (2,101), (2,102), (2,200)
                                ,(3,5), (3,6), (3,13),(3,20), (3,30), (3,51), (3,52), (3,53)

我必须得到以下选择:

HeaderTableId , NumberRanges

'1', |||||||||||||||||||||||| '1-3, 6-8'

'2',|||||||||||||||||||||||| '51-53, 100-102, 200'

'3',|||||||||||||||||||||||| '5-6, 13, 20, 30, 51-53'

我更愿意在一个 select 语句中获取所有内容，而不需要额外的临时表。

谢谢!

Answer 1

;WITH CTE as
(
  SELECT HeaderTableIdFK, DetailTableNumber, 
  DetailTableNumber 
   - row_number() over (partition by HeaderTableIdFK order by DetailTableNumber) grp
  FROM @detailTable
), CTE2 as
(
  SELECT HeaderTableIdFK, min(DetailTableNumber) min, max(DetailTableNumber) max
  FROM CTE
  GROUP BY HeaderTableIdFK, grp
)
SELECT HeaderTableIdFK,
    STUFF(( 
        SELECT ', ' + CASE WHEN [min] < [max] 
          THEN CAST([min] as varchar(18)) + '-' + CAST([max] as varchar(18))
          ELSE CAST([min] as varchar(18))
        END
        FROM CTE2 t1 
        WHERE t1.HeaderTableIdFK = t.HeaderTableIdFK
        for xml path(''), type 
    ).value('.', 'varchar(max)'), 1, 1, '') [NumberRanges] 
FROM CTE2 t 
GROUP BY t.HeaderTableIdFK

结果:

HeaderTableIdFK NumberRanges
1               1-3, 6-8
2               51-53, 100-102, 200
3               5-6, 13-13, 20-20, 30-30, 51-53