复杂SQL语句，高手帮忙！谢谢！
现有三张表 #sl_1, #sl_2,#sl_3,数据如下：
＃sl_1数据:
cwhcode cwh_name sl_1
10 联盟 1
06 龙家 2
03 新法 1
＃sl_2数据:
cwhcode cwh_name sl_2
01 田坝 1
05 土木 1
＃sl_3数据:
cwhcode cwh_name sl_3
01 田坝 1
现在希望得到如下查询结果：
cwhcode cwh_name sl_1 sl_2 sl_3
10 联盟 1 0 0
06 龙家 2 0 0
03 新法 1 0 0
01 田坝 0 1 1
05 土木 0 1 0
肯请高手帮忙，谢谢！
[解决办法]
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)
go
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
[解决办法]
先用union把三个表的cwhcode cwh_name两列数据生成一个全集，再left join 这三个表。
select 列表中用isnull()函数即可。
[解决办法]
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 desc
drop table #sl_1
drop table #sl_2
drop table #sl_3
结果-----
10联盟100
06龙家200
05土木010
03新法100
01田坝011
[解决办法]
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
