复杂SQL语句，高手帮忙！谢谢！

Question

create table sl1(cwhcode varchar(10),cwh_name varchar(10),sl_1 int)
insert sl1 values('10','联盟',1)
insert sl1 values('06','龙家',2)
insert sl1 values('03','新法',1)
create table sl2(cwhcode varchar(10),cwh_name varchar(10),sl_2 int)
insert sl2 values('01','田坝',1)
insert sl2 values('05','土木',1)create table sl3(cwhcode varchar(10),cwh_name varchar(10),sl_3 int)
insert sl3 values('01','田坝',1)goselect a.*,[sl_1]=isnull(b.sl_1,0),[sl_2]=isnull(c.sl_2,0),[sl_3]=isnull(d.sl_3,0) from 
(
select cwhcode,cwh_name from sl1
union
select cwhcode,cwh_name from sl2
union
select cwhcode,cwh_name from sl3
) a left join sl1 b on a.cwhcode=b.cwhcode
left join sl2 c on a.cwhcode=c.cwhcode
left join sl3 d on a.cwhcode=d.cwhcode

Answer 1

先用union把三个表的cwhcode   cwh_name两列数据生成一个全集，再left join 这三个表。select 列表中用isnull()函数即可。

Answer 2

create table #sl_1(cwhcode varchar(10),cwh_name varchar(10),sl_1 int)
insert #sl_1 values('10','联盟',1)
insert #sl_1 values('06','龙家',2)
insert #sl_1 values('03','新法',1)
create table #sl_2(cwhcode varchar(10),cwh_name varchar(10),sl_2 int)
insert #sl_2 values('01','田坝',1)
insert #sl_2 values('05','土木',1)create table #sl_3(cwhcode varchar(10),cwh_name varchar(10),sl_3 int)
insert #sl_3 values('01','田坝',1)
select pub.cwhcode,
       pub.cwh_name,
         isnull(a.sl_1,0),
         isnull(b.sl_2,0),
         isnull(c.sl_3,0)
from (select a.cwhcode,a.cwh_name from #sl_1 a union select b.cwhcode ,b.cwh_name from #sl_2  b union select c.cwhcode,c.cwh_name from #sl_3  c ) pub
left join #sl_1 a on pub.cwhcode=a.cwhcode
left join #sl_2 b on pub.cwhcode=b.cwhcode
left join #sl_3 c on pub.cwhcode=c.cwhcode
order by pub.cwhcode descdrop table #sl_1
drop table #sl_2
drop table #sl_3
结果-----
10 联盟 1 0 0
06 龙家 2 0 0
05 土木 0 1 0
03 新法 1 0 0
01 田坝 0 1 1

Answer 3

使用连接，将字段值‘is null’

Answer 4

if object_id('pubs..sl_1') is not null
   drop table sl_1
go
create table sl_1(cwhcode varchar(10),cwh_name varchar(10),sl_1 int)
insert into sl_1(cwhcode,cwh_name,sl_1) values('10',        '联盟',        1)
insert into sl_1(cwhcode,cwh_name,sl_1) values('06',        '龙家',        2)
insert into sl_1(cwhcode,cwh_name,sl_1) values('03',        '新法',        1)
goif object_id('pubs..sl_2') is not null
   drop table sl_2
go
create table sl_2(cwhcode varchar(10),cwh_name varchar(10),sl_2 int)
insert into sl_2(cwhcode,cwh_name,sl_2) values('01',        '田坝',        1)
insert into sl_2(cwhcode,cwh_name,sl_2) values('05',        '土木',        1)
goif object_id('pubs..sl_3') is not null
   drop table sl_3
go
create table sl_3(cwhcode varchar(10),cwh_name varchar(10),sl_3 int)
insert into sl_3(cwhcode,cwh_name,sl_3) values('01',        '田坝',        1)
go
select cwhcode,cwh_name,max(sl_1) sl_1 , max(sl_2) sl_2 , max(sl_3) sl_3 from
(
  select cwhcode,cwh_name,sl_1 , sl_2 = 0 , sl_3 = 0 from sl_1
  union all
  select cwhcode,cwh_name,sl_1 = 0 , sl_2 , sl_3 = 0 from sl_2
  union all
  select cwhcode,cwh_name,sl_1 = 0 , sl_2 = 0 , sl_3 from sl_3
) t
group by cwhcode,cwh_name
order by cwhcode,cwh_namedrop table sl_1,sl_2,sl_3/*
cwhcode    cwh_name   sl_1        sl_2        sl_3        
---------- ---------- ----------- ----------- ----------- 
01         田坝         0           1           1
03         新法         1           0           0
05         土木         0           1           0
06         龙家         2           0           0
10         联盟         1           0           0（所影响的行数为 5 行）
*/

Answer 5

select * into #t from ( select cwhcode ,cwh_name from sl1 
union select cwhcode ,cwh_name from sl2 
union select cwhcode  ,cwh_name from sl3 )
select #t.cwhcode,#t.cwh_name,isnull(sl_1,0)sl_1,isnull(sl_2,0) sl_2,isnull(sl_3,0) sl_3 from 
#t left join sl1 on #t.cwhcode=sl1.cwhcode left join sl2 on  #t.cwhcode=sl2.cwhcode
left join sl3 on  #t.cwhcode=sl3.cwhcode
 drop table #t

Answer 6

select a.*,sl_1=isnull(b.sl_1,0),sl_2=isnull(c.sl_2,0),sl_3=isnull(d.sl_3,0) from 
(
select cwhcode,cwh_name from sl1
union
select cwhcode,cwh_name from sl2
union
select cwhcode,cwh_name from sl3
) a 
left join sl1 b on a.cwhcode=b.cwhcode
left join sl2 c on a.cwhcode=c.cwhcode
left join sl3 d on a.cwhcode=d.cwhcode

Answer 7

select cwhcode,cwh_name,sl_1,sl_2=0,sl_3=0 from sl1
union
select cwhcode,cwh_name,sl_1=0,sl_2,sl_3=0 from sl2
union
select cwhcode,cwh_name,sl_1=0,sl_2=0,sl_3 from sl3

Answer 8

select cwhcode,cwh_name,sl_1,sl_2=0,sl_3=0 from #sl1
union
select cwhcode,cwh_name,sl_1=0,sl_2,sl_3=0 from #sl2
union
select cwhcode,cwh_name,sl_1=0,sl_2=0,sl_3 from #sl3