发货要求表
产品 要求发货总数量
A 100
B 200
C 150
计划送货表
产品 计划批号 计划数量
A A001 50
A A002 50
B B001 100
B B003 100
C C001 100
C C002 50
实际送货表
产品 实际批号 实际数量
A A001 50
A A003 25
A A004 22
B B005 200
C C003 50
C C004 100
要求出合并的报表
产品 要求发货总数量 计划送货批号 计划送货数量 实际批号 实际数量
A 100 A001 50 A001 50
A 100 A002 50 A003 25
A 100 NULL NULL A004 22
B 200 B001 100 B005 200
B 200 B003 100 NULL NULL
C 150 C001 100 C003 50
C 150 C002 50 CA004 100
比较绕和复杂，谢谢。
------解决思路----------------------
create table T1(
pid varchar(10)
,ct int
)
create table T2(
pid varchar(10)
,pno varchar(10)
,pct int
)
create table T3(
pid varchar(10)
,ano varchar(10)
,act int
)
go
insert into T1(pid,ct) values
('A',100)
,('B',200)
,('C',150)
insert into T2(pid,pno,pct) values
('A','A001',50)
,('A','A002',50)
,('B','B001',100)
,('B','B003',100)
,('C','C001',100)
,('C','C002',50)
insert into T3(pid,ano,act) values
('A','A001',50)
,('A','A003',25)
,('A','A004',22)
,('B','B005',200)
,('C','C003',50)
,('C','C004',100)
--
;with c1 as(
select rn=ROW_NUMBER() over(partition by pid order by ano,getdate()),* from T3
),c2 as (
select rn=ROW_NUMBER() over(partition by pid order by pno,getdate()),* from T2
)
select v.pid,v.ct,b.pno,b.pct,a.ano,a.act from c1 a full join c2 b on a.pid=b.pid and (a.ano=b.pno or a.ano<>b.pno and a.rn=b.rn)
left join T1 v on ISNULL(a.pid,b.pid)=v.pid
order by v.pid
/*
--结果是出来了，不过觉得要求很狗血，很奇怪
pid ct pno pct ano act
---------- ----------- ---------- ----------- ---------- -----------
A 100 A001 50 A001 50
A 100 A002 50 A003 25
A 100 NULL NULL A004 22
B 200 B001 100 B005 200
B 200 B003 100 NULL NULL
C 150 C001 100 C003 50
C 150 C002 50 C004 100
*/
------解决思路----------------------
借用樓上的數據
create table T1(
pid varchar(10)
,ct int
)
create table T2(
pid varchar(10)
,pno varchar(10)
,pct int
)
create table T3(
pid varchar(10)
,ano varchar(10)
,act int
)
go
insert into T1(pid,ct)
select 'A',100 union all
select 'B',200 union all
select 'C',150
insert into T2(pid,pno,pct)
select 'A','A001',50 union all
select 'A','A002',50 union all
select 'B','B001',100 union all
select 'B','B003',100 union all
select 'C','C001',100 union all
select 'C','C002',50
insert into T3(pid,ano,act)
select 'A','A001',50 union all
select 'A','A003',25 union all
select 'A','A004',22 union all
select 'B','B005',200 union all
select 'C','C003',50 union all
select 'C','C004',100
;with sel1 as(
select pid,pno,pct,row_number() over(partition by pid order by pno) as rn from t2)
,sel2 as
(select pid,ano,act,row_number() over(partition by pid order by ano) as rn from t3)
select a.pid,a.ct,t.pno,t.pct,t.ano,t.act
from t1 a
left join
(
select isnull(b.pid,c.pid) pid,b.pno,b.pct,c.ano,c.act
from sel1 b full join sel2 c
on b.pid=c.pid and b.rn=c.rn
) t on a.pid=t.pid