转换数字序列格式，以便将其连字符

Question

我有一系列需要用连字符表示的数字，但不确定如何最好地通过SQL数据库选择来实现这一点。

预期结果：

Peter: 1,3-7,10,11,13  
Andrew: 1-3  
Paul:   1-3  

表中的数据示例(小选择)：

NAME     #
Peter   1  
Andrew  1   
Paul    1  
Andrew  2  
Paul    2  
Peter   3  
Andrew  3  
Paul    3  
Peter   4  
Peter   5  
Peter   6   
Peter   7  

Answer 1

这部分是空白和岛屿，部分字符串聚合。这就确定了以下几类：

select name,
       (case when min(number) = max(number)
             then convert(varchar(max), min(num))
             else concat(min(number), '-', max(number))
        end) as range
from (select name, number,
             row_number() over (partition by name order by number) as seqnum
      from t
     ) t
group by name, (number - seqnum);

使用此方法，您可以添加一个额外的聚合级别以获得最终结果：

select name,
       string_agg(range, ',') within group (order by min(min_number)) as col
from (select name, min(number) as min_number,
             (case when min(number) = max(number)
                   then convert(varchar(max), min(num))
                   else concat(min(number), '-', max(number))
              end) as range
      from (select name, number,
                   row_number() over (partition by name order by number) as seqnum
            from t
           ) t
      group by name, (number - seqnum)
     ) n
group by name;