22,207
社区成员
发帖
与我相关
我的任务
分享
create table A表
(ID1 varchar(10),ID2 varchar(10))
insert into A表
select 'NAME1','AO1' union all
select 'NAME2','AO2' union all
select 'NAME3','AO3'
create table B表
(ID11 varchar(10),ID12 varchar(10),ID13 varchar(10))
insert into B表
select 'NAME1','BO1','BB1' union all
select 'NAME2','BO2','BB2' union all
select 'NAME3','BO3','BB3'
create table C表
(ID22 varchar(10),ID23 varchar(10),ID24 varchar(10))
insert into C表
select 'BO1','C01','C12' union all
select 'BO1','C01','C21' union all
select 'BO1','C02','C22' union all
select 'BO2','C03','C13'
select ID22,ID23,ID24,identity(int,1,1) 'rn'
into #c
from C表
select case when c.rn=1 or c.rn is null then t.ID1 else '' end 'ID1',
case when c.rn=1 or c.rn is null then t.ID2 else '' end 'ID2',
case when c.rn=1 or c.rn is null then t.ID12 else '' end 'ID12',
case when c.rn=1 or c.rn is null then t.ID13 else '' end 'ID13',
isnull(c.ID23,'') 'ID23',
isnull(c.ID24,'') 'ID24'
from (select a.ID1,a.ID2,b.ID12,b.ID13
from A表 a
left join B表 b on a.ID1=b.ID11) t
left join (select x.ID22,x.ID23,x.ID24,
(select count(1) from #c y where y.ID22=x.ID22 and y.rn<=x.rn) 'rn'
from #c x) c on t.ID12=c.ID22
/*
ID1 ID2 ID12 ID13 ID23 ID24
---------- ---------- ---------- ---------- ---------- ----------
NAME1 AO1 BO1 BB1 C01 C12
C01 C21
C02 C22
NAME2 AO2 BO2 BB2 C03 C13
NAME3 AO3 BO3 BB3
(5 row(s) affected)
*/