求SQL数目字连续性分组写法

求SQL数字连续性分组写法

SQL code

--说明:--比如1-3是连续的，作为1组，5-6是连续的作为1组，9单独也作为1组，分组后合并VALUE1的值，用逗号隔开。--原始数据（请考虑支持10000行时的高效写法）DECLARE @TB TABLE(ID INT,VALUE1 VARCHAR(10))INSERT INTO @TBSELECT 2,'b' UNION ALLSELECT 1,'a' UNION ALLSELECT 3,'c' UNION ALLSELECT 5,'e' UNION ALLSELECT 6,'x' UNION ALLSELECT 9,'y'--目标结果（如果难度过大可考虑不合并VALUE1，给出_MIN和_MAX即可）SELECT 1 AS _MIN,3 AS _MAX,'a,b,c' AS ALL_VALUE UNION ALLSELECT 5,5,'e,x' UNION ALLSELECT 9,9,'y'

SQL code

create table tb(value int,name varchar(5))INSERT INTO tbSELECT 2,'b' UNION ALLSELECT 1,'a' UNION ALLSELECT 3,'c' UNION ALLSELECT 5,'e' UNION ALLSELECT 6,'x' UNION ALLSELECT 9,'y'with tas(select px=value-ROW_NUMBER()over(order by value),* from tb)SELECT MIN(a.value)min_value,MAX(a.value) max_value,name=STUFF((SELECT ','+t.nameFROM t WHERE a.px=px FOR XML PATH('')),1,1,'')FROM t aGROUP BY a.px/*min_value    max_value    name1    3    a,b,c5    6    e,x9    9    y*/