例如
id type
1 a
2 b
3 a
4 a
5 a
6 b
7 c
8 a
变成 增加一列的新表
id type type_hz
1 a 1,3,4,5,8
2 b 2,6
3 a 1,3,4,5,8
4 a 1,3,4,5,8
5 a 1,3,4,5,8
6 b 2,6
7 c 7
8 a 1,3,4,5,8
我会用游标写,但是慢所以不要用游标,不要用自定义函数,移植不好
求教
------解决方案--------------------
- SQL code
create table tb( id int, type varchar(1))insert into tbselect 1,'a' union allselect 2,'b' union allselect 3, 'a' union allselect 4, 'a' union allselect 5, 'a' union allselect 6, 'b' union allselect 7, 'c' union allselect 8, 'a'--个人感觉用函数最简单，切移植性更好(只要修改函数即可)create function F_GetType_hz(@typeid varchar(1)) returns varchar(1000) AS begin declare @s varchar(1000) select @s=isnull(@s+',' , '')+ cast(id as varchar) from tb where type [email protected] return @sendselect *,type_hz=dbo.F_GetType_hz(type) from tb
------解决方案--------------------
- SQL code
if not object_id('Tempdb..#T') is null drop table #TGoCreate table #T([id] int,[type] nvarchar(1))Insert #Tselect 1,N'a' union allselect 2,N'b' union allselect 3,N'a' union allselect 4,N'a' union allselect 5,N'a' union allselect 6,N'b' union allselect 7,N'c' union allselect 8,N'a'GoSelect *, stuff((select ','+cast([ID] as varchar(10)) from #t where [type]=t.[type] for xml path('')),1,1,'') from #T t
------解决方案--------------------
- SQL code
create table tb( id int, type varchar(1))insert into tbselect 1,'a' union allselect 2,'b' union allselect 3, 'a' union allselect 4, 'a' union allselect 5, 'a' union allselect 6, 'b' union allselect 7, 'c' union allselect 8, 'a'SELECT *FROM( SELECT id,type FROM tb)AOUTER APPLY( SELECT type_hz= STUFF(REPLACE(REPLACE( ( SELECT id FROM tb B WHERE type = A.type FOR XML AUTO ), '<B id="', ','), '"/>', ''), 1, 1, ''))B
------解决方案--------------------
for sql2000的方法.
- SQL code
create table jic(id int, typei char(1))insert into jicselect 1, 'a' union allselect 2, 'b' union allselect 3, 'a' union allselect 4, 'a' union allselect 5, 'a' union allselect 6, 'b' union allselect 7, 'c' union allselect 8, 'a'-- create functioncreate function fn_typehz(@typei char(1))returns varchar(50)asbegin declare @r varchar(50)='' select @[email protected]+','+cast(id as varchar) from jic where [email protected] return stuff(@r,1,1,'') end-- use functionselect id,typei,dbo.fn_typehz(typei) 'type_hz'from jic-- resultid typei type_hz----------- ----- -------------1 a 1,3,4,5,82 b 2,63 a 1,3,4,5,84 a 1,3,4,5,85 a 1,3,4,5,86 b 2,67 c 78 a 1,3,4,5,8(8 row(s) affected)