忘了怎么搞。。。
根据图一的两个表得到图二的查询
[解决办法]
create table #tb1 (wlcode varchar(50))
create table #tb2 (qty int,wlcode varchar(50),depcode varchar(2))
insert into #tb1
select '10201002' union all
select '10201005' union all
select '10201015' union all
select '10201050'
insert into #tb2
select 32,'10201005',1 union all
select 16,'10201015',1 union all
select 22,'10201050',1 union all
select 55,'10201005',2 union all
select 14,'10201015',2 union all
select 31,'10201050',2
select qty= case when c.qty IS NULL then 0 else c.qty end ,ab.wlcode ,ab.number from
(select a.wlcode ,b.number from #tb1 a join master .dbo.spt_values b on b.number <3 and b.number >0
where b.type='P' ) ab left join #tb2 c on ab.number =c.depcode and ab.wlcode =c.wlcode
order by ab.number ,ab.wlcode
drop table #tb1
drop table #tb2
select
isnull(b.qty,0),a.*
from
(select * from a cross join (select distinct depcode from b) as b) as a
left join b on a.wlcode=b.wlcode
create table #tb1 (wlcode varchar(50))
create table #tb2 (qty int,wlcode varchar(50),depcode varchar(2))
insert into #tb1
select '10201002' union all
select '10201005' union all
select '10201015' union all
select '10201050'
insert into #tb2
select 32,'10201005',1 union all
select 16,'10201015',1 union all
select 22,'10201050',1 union all
select 55,'10201005',2 union all
select 14,'10201015',2 union all
select 32,'10201050',2
select isnull(t2.qty,0) qty,t1.wlcode,t.depcode
from #tb1 t1
inner join
(
select depcode
from #tb2
group by depcode
)t
on 1= 1
left join #tb2 t2
on t1.wlcode = t2.wlcode and
t.depcode = t2.depcode
/*
qtywlcodedepcode
0102010021
32102010051
16102010151
22102010501
0102010022
55102010052
14102010152
32102010502
*/
drop table #tb1
drop table #tb2