sql 合并问题
table1
yid yname
1 张三
2 李四
table2
cyid cynameid
1 22
1 12
2 22
2 33
table3
sid sname
22 北京
33 天津
12 上海
sql语句 合并后效果
yname sname
张三 北京,上海
李四 北京,天津
[解决办法]
create table #table1 (yid int, yname nvarchar(10))
insert #table1
select 1 ,'张三' union all
select 2 ,'李四'
create table #table2 (cyid int , cynameid int)
insert #table2
select 1 , 22 union all
select 1 , 12 union all
select 2 , 22 union all
select 2 , 33
create table #table3 (sid int, sname nvarchar(10))
insert #table3
select 22 ,'北京' union all
select 33 ,'天津' union all
select 12 ,'上海'
--查询
with temp as
(
select t2.cyid,t3.sname from #table2 t2 left join #table3 t3 on t2.cynameid=t3.sid
)
select t1.yname,tt.sname from #table1 t1 left join
(
SELECT cyid,
STUFF((SELECT ','+ sname
FROM temp
WHERE cyid = t.cyid
FOR XML PATH('')),1,1,'') AS sname
FROM temp t GROUP BY cyid
)
tt on t1.yid=tt.cyid
create table #a(yid int,yname varchar(100))
insert into #a select 1,'张三' union all
select 2 ,'李四'
create table #b(cyid int,cynameid int)
insert into #b select 1,22 union all
select 1,12 union all
select 2,22 union all
select 2,33
create table #c([sid] int,sname varchar(100))
insert into #c select 22,'北京' union all
select 33,'天津' union all
select 12,'上海'
--sql语句 合并后效果
--yname sname
--张三 北京,上海
--李四 北京,天津
with ceb as
(
select t2.cyid,t3.sname from #b t2 left join #c t3 on t2.cynameid=t3.sid
)
select a.yname,c.sname from #a a,
(
select b.cyid, stuff((select (','+c.sname) from ceb c where b.cyid=c.cyid for xml path('')),1,1,'') as sname
from #b b group by b.cyid
) c where a.yid=c.cyid
不好意思，中间多了一条查询，去掉就可以了，先创表，在试下面的查询语句。。
