最小最大逗号分隔范围在SQL服务器中，在一个单元格中具有不同的范围

Question

我使用 Stuff 编写了这个查询来生成范围，但由于输入时间分组，如果同时进入不同的范围，那么它找不到范围的最小最大值

SELECT DISTINCT ST2.Center_Code
    ,STUFF((
            SELECT ' , ' + ST1.Ranges
            FROM (
                SELECT Center_Code
                    ,CAST(MIN(CONVERT(BIGINT, Starting_No)) AS VARCHAR(9)) + '-' + CAST(MAX(CONVERT(BIGINT, Starting_No)) AS VARCHAR(9)) Ranges
                FROM AwardSheet_Record
                GROUP BY Center_Code
                    ,LEFT(Despatch_Time, 8)
                ) ST1
            WHERE ST1.Center_Code = ST2.Center_Code
            ORDER BY ST1.Center_Code
            FOR XML PATH('')
            ), 1, 2, '') Center_Range
FROM AwardSheet_Record ST2
WHERE ST2.Center_Code IS NOT NULL

表中记录是这样的

CenterCode   starting    time
  123           1       2:30
  123           2       2:30
  123           3       2:30
  14            6       2:33
  14            7       2:33
  14            8       2:33
  123           9       2:35
  123           10      2:35
  14            11      2:38
  14            12      2:38
  14            13      2:38

所需范围适用于中心 123 1-2,8-10 和中心 14 6-8,11-13

Answer 1

我会分阶段进行：

With AwardSheet_Record As (
    Select CenterCode, starting, cast(Time as Time) As Time
    From (Values 
        ((123 ),(1 ),('2:30')),
        ((123 ),(2 ),('2:30')),
        ((123 ),(3 ),('2:30')),
        ((14  ),(6 ),('2:33')),
        ((14  ),(7 ),('2:33')),
        ((14  ),(8 ),('2:33')),
        ((123 ),(9 ),('2:35')),
        ((123 ),(10),('2:35')),
        ((14  ),(11),('2:38')),
        ((14  ),(12),('2:38')),
        ((14  ),(13),('2:38'))
    ) a (CenterCode, starting, Time)
), AwardSheet_Intervals As (
    Select CenterCode, Time, Cast(Min(starting) As Varchar) + ' - ' + Cast(Max(starting) As Varchar) As Interval
    From AwardSheet_Record
    Group By CenterCode, Time
)
Select CenterCode,
  STUFF((
    Select ', ' + Interval
    From AwardSheet_Intervals
    Where CenterCode = Ref.CenterCode --< Apply GROUP BY filtering here
    Order By Time
    For XML PATH (''), TYPE
  ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') As [Range] --< XML trick to combine values in a comma-separated string
From AwardSheet_Intervals Ref
Group By CenterCode;

---

CenterCode  Range
----------  --------------
        14  6 - 8, 11 - 13
       123  1 - 3, 9 - 10