父子结构的表输出问题
有3个表
表一 主表
AID UserName
1 a
2 b
表二 副表
BID LevelName
1 aa
2 bb
3 aaa
4 bbbb
5 ccccc
表三 关系表
ID AID BID
1 1 1
2 1 2
3 2 3
4 2 4
5 2 5
现在 一个UserName 有多个关联多个LevelName
怎么在一行记录中输出呢
例如
AID UserName LevelName
1 a aa,bb
2 b aaa,bbbb,ccccc
[解决办法]
if object_id('Tempdb..#A') is not null drop table #A
if object_id('Tempdb..#B') is not null drop table #B
if object_id('Tempdb..#C') is not null drop table #C
--临时表A
create table #A
(
[AID] int identity(1,1) not null,
[UserName] nvarchar(10) null
)
--临时表B
create table #B
(
[BID] int identity(1,1) not null,
[LevelName] nvarchar(10) null
)
--临时表C
create table #C
(
id int identity(1,1) not null,
[AID] int null,
[BID] int null
)
-----------添加测试数据---------
Insert into #A
select 'a' union all
select 'b'
Insert into #B
select 'aa' union all
select 'bb' union all
select 'aaa' union all
select 'bbbb' union all
select 'ccccc'
Insert into #C
select 1,1 union all
select 1,2 union all
select 2,3 union all
select 2,4 union all
select 2,5
--
-------------查询-------------
;with cte as(
select a.AID,a.UserName,b.LevelName from #a a
join #c c on a.AID=c.AID
join #b b on b.BID=c.BID
)
select AID,UserName, stuff((select ','+LevelName from cte z where t.AID=z.AID for xml path('')), 1, 1, '') LevelName
from cte t
group by AID,UserName
-------------------------------------------
----------结果------------
AID UserName LevelName
----------- ---------- ----------------------------------------------------------------------------------------------------------------
1 a aa,bb
2 b aaa,bbbb,ccccc
(2 行受影响)
create table 主表
(AID int,UserName varchar(10))
insert into 主表
select 1, 'a' union all
select 2, 'b'
create table 副表
(BID int, LevelName varchar(10))
insert into 副表
select 1, 'aa' union all
select 2, 'bb' union all
select 3, 'aaa' union all
select 4, 'bbbb' union all
select 5, 'ccccc'
create table 关系表
(ID int, AID int, BID int)
insert into 关系表
select 1, 1, 1 union all
select 2, 1, 2 union all
select 3, 2, 3 union all
select 4, 2, 4 union all
select 5, 2, 5
select a.AID,
a.UserName,
stuff((select ','+LevelName
from 关系表 c
inner join 副表 b on c.BID=b.BID
where c.AID=a.AID
for xml path('')),1,1,'') 'LevelName'
from 主表 a
/*
AID UserName LevelName
----------- ---------- ---------------------
1 a aa,bb
2 b aaa,bbbb,ccccc
(2 row(s) affected)
*/
