sql - 按列顺序获取相同的范围值

Question

我有一张 table

ID Value
1  2
2  3
3  3
4  3
5  2
6  2
7  1

我想对值进行分组，但仍然按 ID 排序，如下所示

ID   Value
1    2
2-4  3
5-6  2
7    1

我该怎么做？

Answer 1

DECLARE @t TABLE (
    ID INT PRIMARY KEY,
    Value INT
)

INSERT INTO @t (ID, Value)
VALUES (1,2),(2,3),(3,3),(4,3),(5,2),(6,2),(7,1)

SELECT 
    ID =
        CASE WHEN mx = mn
            THEN CAST(mx AS VARCHAR(10))
            ELSE CAST(mn AS VARCHAR(10)) + '-' + CAST(mx AS VARCHAR(10))
        END,
    Value
FROM (
    SELECT group_id, mx = MAX(ID), mn = MIN(ID), Value = MAX(Value)
    FROM (
        SELECT
              *
            , group_id =
                ROW_NUMBER() OVER (ORDER BY ID) -
                ROW_NUMBER() OVER (PARTITION BY Value ORDER BY ID)
        FROM @t
    ) t
    GROUP BY group_id
) t

输出 -

ID    Value
----- -----------
1     2
2-4   3
5-6   2
7     1