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
张三 北京,上海
李四 北京,天津
yid yname
1 张三
2 李四
table2
cyid cynameid
1 22
1 12
2 22
2 33
table3
sid sname
22 北京
33 天津
12 上海 sql语句 合并后效果
yname sname
张三 北京,上海
李四 北京,天津
解决方案 »
- 借这里人气 问个Expression encode 的问题
- 服务端的一个按钮 加OnClientClick事件 进来看看
- 我用javascript给一个textbox设置值,为什么在codebehind里取不到这个值?
- asp直接生成word文件出错,急。。。。。。
- 基础问题 字符串连接 & +
- 组合查询问题?归求大家帮忙?
- 请问怎么能让windows2003支持cgi的网页?
- 这是用VB写的统计在线人数的例子,有几处不太懂,请指教!!!!
- 在C#中怎样使用vb.net中的代码?
- 在asp.net中能否捕捉用户关闭该页面浏览器的事件??
- vs 中的installshied2012,怎么制作更新包
- ASP.net用Session做购物车的问题
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 #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
yname sname
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
张三 北京,上海
李四 北京,天津(2 行受影响)
这是2段sql?? 一段能查出来么?
select t1.yname,t3.sname into #t from table1 t1
inner join table2 t2 on t1.yid=t1.cyid
inner join table3 t3 on t3.sid=cynameid) as tselect yneme,[values]=stuff((select ','+[value] from tb #t where id=tb.id for xml path('')), 1, 1, '')
from #t
group by yname
drop table #t
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 select * from #bcreate 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
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
不好意思,中间多了一条查询,去掉就可以了,先创表,在试下面的查询语句。。