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
with tb as ( select distinct depcode from t2 ) , tb2 as ( select wlcode from t1 except select wlcode from t2 ) select * from t2 union all select 0, wlcode, depcode from tb2,tb
select isnull(b.qty,0),a.* from (select wlcode from a cross join (select distinct depcode) as b)a left join b on a.wlcode=b.wlcode
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
select t2.* from t1 inner join t2 on t1.wlcode = t2.wlcode union all select 0,a.wlcode,b.depcode from ( select t1.* from t1 left join t2 on t1.wlcode = t2.wlcode where t2.qty is null ) a cross ( select depcode from t2 ) b
把上面小人鱼的代码,改了一下: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 /* qty wlcode depcode 0 10201002 1 32 10201005 1 16 10201015 1 22 10201050 1 0 10201002 2 55 10201005 2 14 10201015 2 32 10201050 2 */drop table #tb1 drop table #tb2
isnull(column,0) 后面给0 。。就都是0了啊这个是1或者2。。上面只是个例子,数据还很多呢
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 distinct depcode from t2
)
, tb2 as
(
select wlcode from t1
except
select wlcode from t2
)
select * from t2
union all
select 0, wlcode, depcode
from tb2,tb
isnull(b.qty,0),a.*
from
(select wlcode from a cross join (select distinct depcode) as b)a
left join b on a.wlcode=b.wlcode
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
inner join t2
on t1.wlcode = t2.wlcode
union all
select 0,a.wlcode,b.depcode from
(
select t1.* from t1
left join t2
on t1.wlcode = t2.wlcode
where t2.qty is null
) a cross
(
select depcode from t2
) b
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
/*
qty wlcode depcode
0 10201002 1
32 10201005 1
16 10201015 1
22 10201050 1
0 10201002 2
55 10201005 2
14 10201015 2
32 10201050 2
*/drop table #tb1
drop table #tb2
...