Server中按多个范围分组

Question

我试图寻找解决办法，但没有成功。我怎样才能把我的桌子按这样的样子分组：

from    |    to    |    zone
1       |    1     |      1
1       |    2     |      1
1       |    3     |      1
1       |    4     |      2
1       |    5     |      2
1       |    6     |      2
1       |    7     |      1
1       |    8     |      1
1       |    9     |      1
1       |    10    |      9
2       |    1     |      7
2       |    2     |      7
2       |    3     |      7
2       |    4     |      2
2       |    5     |      2
2       |    6     |      2
2       |    7     |      7
2       |    8     |      7
2       |    9     |      7

像这样：

from    |    to      |    zone
1       |    1-3     |      1
1       |    4-6     |      2
1       |    7-9     |      1
1       |     10     |      9
2       |    1-3     |      7
2       |    4-6     |      2
2       |    7-9     |      7

谢谢你的帮助

Answer 1

这里的一种方法是使用行号的差异方法，使用to to列作为一行号，使用from和zone作为另一行号的分区上的行号。要用这么多的话来解释为什么这样做有点困难。最好查看下面的演示链接来查看查询。

WITH cte AS (
    SELECT *,
       ROW_NUMBER() OVER (PARTITION BY [from], zone ORDER BY [to]) rn
    FROM yourTable
)
SELECT
    t.[from],
    CONVERT(varchar(10), MIN(t.[to])) + '-' + CONVERT(varchar(10), MAX([to])) AS [to],
    t.zone
FROM cte t
GROUP BY
    t.[from],
    t.zone,
    t.[to] - t.rn
ORDER BY
    t.[from],
    MIN(t.[to]);

演示这里：

​

Rextester

​

Answer 2

这通常被称为差距和岛屿问题。如果您正在使用SQL Server 2012+，那么

;WITH cte
     AS (SELECT *,
                Sum(CASE WHEN zone = prev_zone THEN 0 ELSE 1 END)OVER(partition BY [from] ORDER BY [to]) AS grp
         FROM   (SELECT *,
                        Lag(zone)OVER(partition BY [from] ORDER BY [to]) AS prev_zone
                 FROM   yourtable ) cs ([from], [to], zone)) a)
SELECT [from],
       [to] = Concat(Min([to]), '-', Max([to])),
       zone = Min(zone) 
FROM   cte
GROUP  BY [from],grp

Answer 3

;with mycte
AS
(
select
    ,[from]
    ,min([to]) minto
    ,max([to]) maxto
    ,[zone]
from
    mytable
group by
    [from]
    ,[zone]
)
    [from]                      AS [from]
    ,concat(minto, '-', maxto)  AS [to]
    ,[zone]                     AS [zone]
from
    mycte