T-SQL查询帮助 - 查找表中的范围

I have a table as below. I need the result in the range of name as mentioned below. Kindly provide T-SQL query

我有一张桌子如下。我需要在下面提到的名称范围内的结果。请提供T-SQL查询

ID  Name
101 AA1
102 AA1
103 AA1
104 AA2
105 AA2
106 AA3
107 AA1
108 AA1
109 AA2
110 AA2
111 AA2
112 AA4

Need result as

需要结果为

Name Range
AA1  101-103
AA2  104-105
AA3  106
AA1  107-108
AA2  109-111
AA4  112

3 个解决方案

#1

You can do this using the difference of row numbers approach:

你可以使用行数方法的不同来做到这一点：

select name, min(id), max(id)
from (select t.*,
             row_number() over (order by id) as seqnum,
             row_number() over (partition by name order by id) as seqnum_n
      from t
     ) t
group by name, (seqnum - seqnum_n);

It is a bit hard to explain how this works. The difference of row numbers is constant for names that have consecutive ids. If you run the subquery, you'll see what the row numbers look like and how the difference does what you want.

有点难以解释这是如何工作的。对于具有连续ID的名称，行号的差异是常量。如果运行子查询，您将看到行号的样子以及差异如何实现。

#2

Simply use concat with group by as following:

只需使用concat with group by：

SELECT NAME, CONCAT(MIN(id), '-', MAX(id)) range
FROM Table1
GROUP BY NAME

#3

Use with Statement,PARTITION BY and Group By:

与Statement，PARTITION BY和Group By一起使用：

;WITH T
     AS (SELECT *,
                ID - ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [ID]) AS Grp
         FROM   @tbl1)
SELECT [Name],
       CAST(MIN(Id) AS VARCHAR(5))+'-'+CAST(MAX(Id) AS VARCHAR(5)) AS Range
FROM   T
GROUP  BY [Name],
          Grp
ORDER  BY MIN(ID)

#1