SQL将数字列表转换为一列中的范围

Question

如果我在一个表中有一个范围列表，例如

ID    Number
1       4
1       5
1       6
1       7
1       9

有没有办法用SQL把'4-7,9‘变成一个varchar列？

谢谢。

Answer 1

您可以使用ROW_NUMBER和XML PATH

DECLARE @Mock TABLE (Id INT, Number INT)
INSERT INTO @Mock        
VALUES  
(1, 4),
(1, 5),
(1, 6),
(1, 7),
(1, 9)   

;WITH CTE
AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY Number) AS  RowId,*
    FROM @Mock
)

SELECT  
    STUFF(
    (
        SELECT          
            ',' + CAST(MIN(C.Number) AS VARCHAR(10)) + CASE WHEN MIN(C.Number) = MAX(C.Number) THEN '' ELSE '-' + CAST(MAX(C.Number) AS VARCHAR(10)) END
        FROM
            CTE C
        GROUP BY            
            C.Number - C.RowId
        FOR XML PATH ('')

    ), 1, 1, '') Result

输出：4-7,9

Answer 2

考虑到您有另一个方法来查找范围的顺序

;WITH cte
     AS (SELECT *,
                Sum(CASE
                      WHEN number = prev_lag + 1 THEN 0
                      ELSE 1
                    END)
                  OVER(
                    ORDER BY iden_col) AS grp
         FROM   (SELECT *,
                        Lag(number)
                          OVER(
                            partition BY [ID]
                            ORDER BY iden_col) AS prev_lag
                 FROM   Yourtable)a),
     intr
     AS (SELECT id,
                CASE
                  WHEN Min(number) = Max(number) THEN Cast(Min(number) AS VARCHAR(50))
                  ELSE Concat(Min(number), '-', Max(number))
                END AS intr_res
         FROM   cte
         GROUP  BY id,
                   grp)
SELECT DISTINCT Id,
                Stuff(concat_col, 1, 1, '')
FROM   intr a
       CROSS apply (SELECT ',' + intr_res
                    FROM   intr b
                    WHERE  a.ID = b.ID
                    FOR xml path('')) cs (concat_col) 

• Demo