转换编号规则格式，使其被连字符

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;