有四张表,结构如下：
A(订单)
A_1(订单号) A_2 (订单数量) A_3(订单日期)
a01 100 20150701
a02 50 20150702
B(借出单)
B_1(借出单号) B_2(借出数量) B_3(订单号) B_4(借出日期)
b01 20 a01 20150705
b02 30 a01 20150710
b03 50 a01 20150715
b04 50 b02 20150731
C(出货单)
C_1(出货单号) C_2(出货数量) C_3(订单号) C_4(出货日期)
c01 50 a01 20150801
c02 30 a01 20150805
c03 10 a01 20150820
c04 50 a02 20150830
D(发票)
D_1(发票号) D_2(发票数量) D_3(出货单号) D_4(发票日期)
d01 50 c01 20150801
d02 20 c02 20150810
d03 10 c03 20150820
此时，我想以借出单作为主表进行查询（一定是先有借出再出货），查询出每张借出单的出货数量、发票数量。
我的思路是这样的，因为出货单和发票与借出单没有直接关联 ，所以可以先按订单号去统计sum(出货数量)和sum(发票数量) group by A_1，然后再去满足每张借出单的出货数量(以借出时间去判断，先借出先出货)， 发票逻辑也是一样的。
得出如下结果 ：
借出单号 借出数量 出货数量 发票数量
b01 20 20 20
b02 30 30 30
b03 50 40 30
b04 50 50 0
这样可以做到吗 ？或者有没有更好的方式？
------解决思路----------------------
WITH
/* 测试数据
B(B_1,B_2,B_3,B_4)AS(
SELECT 'b01',20,'a01',20150705 UNION ALL
SELECT 'b02',30,'a01',20150710 UNION ALL
SELECT 'b03',50,'a01',20150715 UNION ALL
SELECT 'b04',50,'a02',20150731 -- 更正了这里的订单号
)
,C(C_1,C_2,C_3,C_4)AS(
SELECT 'c01',50,'a01',20150801 UNION ALL
SELECT 'c02',30,'a01',20150805 UNION ALL
SELECT 'c03',10,'a01',20150820 UNION ALL
SELECT 'c04',50,'a02',20150830
)
,D(D_1,D_2,D_3,D_4)AS(
SELECT 'd01',50,'c01',20150801 UNION ALL
SELECT 'd02',20,'c02',20150810 UNION ALL
SELECT 'd03',10,'c03',20150820
), */
X AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY B_3 ORDER BY B_4,B_1) rn
FROM B
)
,Y AS (
SELECT C_3,
SUM(C_2) C_2,
SUM(D_2) D_2
FROM C
LEFT JOIN D
ON D.D_3 = C.C_1
GROUP BY C_3
)
,Z AS (
SELECT X.B_1 借出单号,
X.B_2 借出数量,
CASE WHEN ISNULL(Y.C_2,0) >= X.B_2
THEN X.B_2
ELSE ISNULL(Y.C_2,0)
END 出货数量,
CASE WHEN ISNULL(Y.D_2,0) >= X.B_2
THEN X.B_2
ELSE ISNULL(Y.D_2,0)
END 发票数量,
CASE WHEN ISNULL(Y.C_2,0) >= X.B_2
THEN ISNULL(Y.C_2,0) - X.B_2
ELSE 0
END 剩余出货数量,
CASE WHEN ISNULL(Y.D_2,0) >= X.B_2
THEN ISNULL(Y.D_2,0) - X.B_2
ELSE 0
END 剩余发票数量,
X.B_3 订单号,
X.rn
FROM X
LEFT JOIN Y
ON X.B_3 = Y.C_3
WHERE X.rn = 1
UNION ALL -- 递归
SELECT X.B_1 借出单号,
X.B_2 借出数量,
CASE WHEN Z.剩余出货数量 >= X.B_2
THEN X.B_2
ELSE Z.剩余出货数量
END 出货数量,
CASE WHEN Z.剩余发票数量 >= X.B_2
THEN X.B_2
ELSE Z.剩余发票数量
END 发票数量,
CASE WHEN Z.剩余出货数量 >= X.B_2
THEN Z.剩余出货数量 - X.B_2
ELSE 0
END 剩余出货数量,
CASE WHEN Z.剩余发票数量 >= X.B_2
THEN Z.剩余发票数量 - X.B_2
ELSE 0
END 剩余发票数量,
X.B_3 订单号,
X.rn
FROM Z
JOIN X
ON X.B_3 = Z.订单号
AND X.rn = Z.rn + 1
)
SELECT 借出单号,
借出数量,
出货数量,
发票数量
FROM Z
ORDER BY 订单号, rn
借出单号 借出数量 出货数量 发票数量
-------- ----------- ----------- -----------
b01 20 20 20
b02 30 30 30
b03 50 40 30
b04 50 50 0
------解决思路----------------------
create table B(B_1 varchar(4),B_2 int,B_3 varchar(4),B_4 varchar(8))
insert into B SELECT 'b01',20,'a01',20150705 UNION ALL
SELECT 'b02',30,'a01',20150710 UNION ALL
SELECT 'b03',50,'a01',20150715 UNION ALL
SELECT 'b04',50,'a02',20150731
create table C(C_1 varchar(4),C_2 int,C_3 varchar(4),C_4 varchar(8))
insert into C SELECT 'c01',50,'a01',20150801 UNION ALL
SELECT 'c02',30,'a01',20150805 UNION ALL
SELECT 'c03',10,'a01',20150820 UNION ALL
SELECT 'c04',50,'a02',20150830
create table D(D_1 varchar(4),D_2 int,D_3 varchar(4),D_4 varchar(8))
insert into D SELECT 'c01',50,'a01',20150801 UNION ALL
SELECT 'd01',50,'c01',20150801 UNION ALL
SELECT 'd02',20,'c02',20150810 UNION ALL
SELECT 'd03',10,'c03',20150820
select * from B
select B_1 借出单号 ,b_2 借借出数量,
出货数量=case when sum_c_2>=sum_b_2 then b_2 else b_2-sum_b_2+sum_c_2 end
,发票数量
=case when sum_d_2>=sum_b_2 then b_2 else b_2-sum_b_2+sum_d_2 end
from
(select v.*,
sum_c_2=(select SUM(c_2)from c where C_3=v.B_3 and C_4>v.B_4 )
,sum_b_2=(select SUM(B_2)from b where B_3=v.b_3 and B_2<=v.b_2)
from b v) m left join
(select c_3,sum_d_2=sum(d_2) from (select * from c left join d on d_3=c_1)n group by c_3) x
on B_3=c_3
借出单号 借出数量 出货数量 发票数量
b01 20 20 20
b02 30 30 30
b03 50 40 30
b04 50 50 NULL