表 a：
编号 值
000055 2
000057 2
000059 2
000060 2
000061 2
000062 2
000063 2
000064 2
000065 3
000066 1
000600 1
要求得到如下结果：
编号 值
000055,000057,000059-000064 2
000065 3
000066,000600 1
将值相同的记录合并成一行，同时将连续的编号写成“起始编号-结束编号”的形式。
------解决方案--------------------
- SQL code
--------------------------------------- Author : liangCK 梁爱兰-- Comment: 小梁 爱 兰儿-- Date : 2009-09-17 16:53:30------------------------------------- --> 生成测试数据: @tbDECLARE @tb TABLE (编号 varchar(6),值 int)INSERT INTO @tbSELECT '000055',2 UNION ALLSELECT '000057',2 UNION ALLSELECT '000059',2 UNION ALLSELECT '000060',2 UNION ALLSELECT '000061',2 UNION ALLSELECT '000062',2 UNION ALLSELECT '000063',2 UNION ALLSELECT '000064',2 UNION ALLSELECT '000065',3 UNION ALLSELECT '000066',1 UNION ALLSELECT '000600',1--SQL查询如下:;WITH Liang AS( SELECT ROW_NUMBER() OVER(PARTITION BY 值 ORDER BY 值) - 编号 AS rowid,* FROM @tb),Liang2 AS( SELECT 值,rowid,CASE WHEN COUNT(*) > 1 THEN RTRIM(MIN(编号))+'~'+RTRIM(MAX(编号)) ELSE RTRIM(MIN(编号)) END AS flag FROM Liang GROUP BY 值,rowid)SELECT STUFF((SELECT ',' + flag AS [text()] FROM Liang2 WHERE 值 = A.值 FOR XML PATH('')),1,1,'') AS 编号, 值FROM Liang2 AS AGROUP BY 值/*编号 值000600,000066 1000059~000064,000057,000055 2000065 3*/
------解决方案--------------------
- SQL code
/*----------------------------------- Author : htl258(Tony)-- Date : 2009-09-17 16:53:11-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)---------------------------------*/--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb]GOCREATE TABLE [tb]([编号] NVARCHAR(10),[值] INT)INSERT [tb]SELECT '000055',2 UNION ALLSELECT '000057',2 UNION ALLSELECT '000059',2 UNION ALLSELECT '000060',2 UNION ALLSELECT '000061',2 UNION ALLSELECT '000062',2 UNION ALLSELECT '000063',2 UNION ALLSELECT '000064',2 UNION ALLSELECT '000065',3 UNION ALLSELECT '000066',1 UNION ALLSELECT '000600',1GO--SELECT * FROM [tb]-->SQL查询如下:;WITH t AS( SELECT CASE WHEN a.编号=b.编号 THEN a.编号 ELSE a.编号+'-'+b.编号 END AS 编号,a.值 FROM ( SELECT rn=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM tb t WHERE NOT EXISTS( SELECT 1 FROM tb WHERE [值]=t.值 AND [编号]=t.[编号]+1) ) AS a JOIN ( SELECT rn=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM tb t WHERE NOT EXISTS( SELECT 1 FROM tb WHERE [值]=t.值 AND t.[编号]=[编号]+1) ) AS b ON a.rn=b.rn) SELECT DISTINCT 编号=STUFF((SELECT ','+编号 FROM t WHERE 值=a.值 FOR XML PATH('')),1,1,''),值FROM t a/*编号 值000055,000057,000064-000059 2000065 3000066,000600 1*/
------解决方案--------------------
- SQL code
sql2000:CREATE TABLE tgss(a VARCHAR(20),b INT)INSERT tgss SELECT '000055',2 union all select '000057', 2 union all select '000059', 2 union all select '000060', 2 union all select '000061', 2 union all select '000062', 2 union all select '000063', 2 union all select '000064', 2 union all select '000065', 3 union all select '000066', 1 union all select '000600', 1ALTER TABLE tgss ADD g INTDECLARE @i INT,@l VARCHAR(20),@sql VARCHAR(2000) SET @i=0UPDATE tgss SET @i=CASE WHEN [email protected]*1+1 THEN @i ELSE @i+1 end,@l=a,[email protected]goSELECT b,g,CASE WHEN MIN(a)=MAX(a) THEN MIN(a) ELSE MIN(a)+'-'+MAX(a) END e into tgssA FROM tgss GROUP BY b,ggoCREATE FUNCTION ltgss(@b INT )RETURNS VARCHAR(1000)AS BEGIN declare @sql VARCHAR(1000) SELECT @sql=ISNULL(@sql+',','')+e FROM tgssA WHERE [email protected] ORDER BY g RETURN @sqlENDgoSELECT b,dbo.ltgss(b) ee FROM tgssA GROUP BY b--result/*b ee ----------- ------------------------------ 1 000066,0006002 000055,000057,000059-0000643 000065（所影响的行数为 3 行）*/