我有数字,但没有顺序。如果这些都是顺序的,那么结果应该如下所示。如果我有像1,2,3,6,8,9,10,12等数据我需要的结果是1-3,6,8-10,12
发布于 2015-07-13 05:59:57
declare @Digits table (Value int primary key)
INSERT @Digits VALUES (1),(2),(3),(6),(8),(9),(10),(12)
SELECT STUFF(CONVERT(varchar(max),
(
SELECT
',' + CASE
WHEN MIN(Value) = MAX(Value) THEN CONVERT(varchar(10), MIN(Value))
ELSE CONVERT(varchar(10), MIN(Value)) + '-' + CONVERT(varchar(10), MAX(Value))
END
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Value) AS Seq, Value FROM @Digits) Data
GROUP BY Value - Seq
FOR XML PATH('')
)), 1, 1, '') AS Result
SQL Fiddle
https://stackoverflow.com/questions/31376088
复制相似问题