求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*/