有两张表：
A表：
-----------------
name pos class
g1 11 chr1
g2 21 chr1
g3 05 chr2
g4 15 chr1
-------------------
B表：
------------------
name pos class
g21 1 chr1
g22 20 chr1
g23 6 chr2
g24 20 chr1
------------------
要求是：A.class=B.class and B.pos<A.pos 并将查询出来的A.name存到 新B.insert中。 查询后的结果新B表为：
------------------
name pos class insert
g21 1 chr1 g1,g2,g4
g22 2 chr1 g2
g23 6 chr2
g24 20 chr1 g2
------------------
求实现的SQL语句，谢谢！
------解决方案--------------------
- SQL code
select *, [insert]=stuff((select ','+name from A表 where class=a.class and b.pos<pos for xml path('')),1,1,'')from B表 b
------解决方案--------------------
- SQL code
create table a1(name varchar(20), pos varchar(20), class varchar(20))insert into a1 values('g1','11','chr1')insert into a1 values('g2','21','chr1')insert into a1 values('g3','05','chr2')insert into a1 values('g4','15','chr1') -------------------create table b1(name varchar(20), pos varchar(20), class varchar(20),[insert] varchar(20))insert into b1 values('g21','1','chr1',null)insert into b1 values('g22','20','chr1',null)insert into b1 values('g23','6','chr2',null)insert into b1 values('g24','20','chr1',null) select * from b1update b1 set[insert]=e.myvaluefrom (select B.name, myvalue=stuff((select ','+aname from(select A0.name as aname,B0.name as bname from a1 as A0,b1 as B0 where A0.class=B0.class and B0.pos<A0.pos)dwhere bname=B.name for xml path('') ), 1 , 1 , '') from b1 as B group by B.name)e where e.name=b1.name select * from b1 drop table a1,b1(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)name pos class insert-------------------- -------------------- -------------------- --------------------g21 1 chr1 NULLg22 20 chr1 NULLg23 6 chr2 NULLg24 20 chr1 NULL(4 行受影响)(4 行受影响)name pos class insert-------------------- -------------------- -------------------- --------------------g21 1 chr1 g1,g4,g2g22 20 chr1 g2g23 6 chr2 NULLg24 20 chr1 g2(4 行受影响)
------解决方案--------------------
- 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