假如表：mycs中列id csa csb 临时表：#lstab中列id aid
问题：mycs表如下
id csa csb
1 abc aac
2 abc aab
3 abd abb
4 abd abb
要求查出mycs表中csa列相同值的id值，并存入#lstab中，如下所示：
id aid
1 1,2
2 3,4
------解决方案--------------------
- SQL code
declare @mysc table( id int, csa varchar(10), csb varchar(10));insert into @mysc values(1,'abc','aac');insert into @mysc values(2,'abc','aac');insert into @mysc values(3,'abd','abb');insert into @mysc values(4,'abd','abb');;with c as( select id,csa from @mysc)select ROW_NUMBER() over(order by(c1.id)) as id, cast(c1.id as varchar(10))+','+cast(c2.id as varchar(10)) as aid --INTO #lsttab --将查询的结果插入临时表from c c1 join c c2 on c1.csa=c2.csa where c1.id<>c2.id and c2.id>c1.id
------解决方案--------------------
- SQL code
;with f as(select row_number() over(order by(id)) as id, [aid]=stuff((select ','+[id] from tb where csa=t.csa for xml path('')), 1, 1, '') from tb tgroup by id)insert into mycs select * from f
------解决方案--------------------
- SQL code
--> 测试数据: [mycs]if object_id('[mycs]') is not null drop table [mycs]create table [mycs] (id int,csa varchar(3),csb varchar(3))insert into [mycs]select 1,'abc','aac' union allselect 2,'abc','aab' union allselect 3,'abd','abb' union allselect 4,'abd','abb'--开始查询select id=identity(int,1,1),aid=stuff((select ','+ltrim(id) from mycs where csa=a.csa for xml path('')),1,1,'') into #lstabfrom mycs a group by csaselect * from #lstab--结束查询drop table [mycs],#lstab/*id aid----------- -------------------1 1,22 3,4(2 行受影响)
------解决方案--------------------
- SQL code
/*标题：按某字段合并字符串之一(简单合并)作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)时间：2008-11-06地点：广东深圳描述：将如下形式的数据按id字段合并value字段。id value----- ------1 aa1 bb2 aaa2 bbb2 ccc需要得到结果：id value------ -----------1 aa,bb2 aaa,bbb,ccc即：group by id, 求 value 的和（字符串相加）*/--1、sql2000中只能用自定义的函数解决create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')gocreate function dbo.f_str(@id varchar(10)) returns varchar(1000)asbegin declare @str varchar(1000) select @str = isnull(@str + ',' , '') + cast(value as varchar) from tb where id = @id return @strendgo--调用函数select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_strdrop table tb--2、sql2005中的方法create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')goselect id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')from tbgroup by iddrop table tb--3、使用游标合并数据create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')godeclare @t table(id int,value varchar(100))--定义结果集表变量--定义游标并进行合并处理declare my_cursor cursor local forselect id , value from tbdeclare @id_old int , @id int , @value varchar(10) , @s varchar(100)open my_cursorfetch my_cursor into @id , @valueselect @id_old = @id , @s=''while @@FETCH_STATUS = 0begin if @id = @id_old select @s = @s + ',' + cast(@value as varchar) else begin insert @t values(@id_old , stuff(@s,1,1,'')) select @s = ',' + cast(@value as varchar) , @id_old = @id end fetch my_cursor into @id , @valueENDinsert @t values(@id_old , stuff(@s,1,1,''))close my_cursordeallocate my_cursorselect * from @tdrop table tb