有如下费用表A:
fid fee
1 10
2 20
3 30
4 40预存款表B:
cid cmoney
1 40
2 30
3 20
1 5现在用预存款来给费用消账;预存款的顺序支付按照cid,费用的消账顺序按照id;
希望得到消账的明细如下:
fid fee cid cpayfee
1 10 1 10
2 20 1 20
3 30 1 10
3 30 2 20
4 40 2 10
4 40 3 20
4 40 4 4这样的sql怎么写?
fid fee
1 10
2 20
3 30
4 40预存款表B:
cid cmoney
1 40
2 30
3 20
1 5现在用预存款来给费用消账;预存款的顺序支付按照cid,费用的消账顺序按照id;
希望得到消账的明细如下:
fid fee cid cpayfee
1 10 1 10
2 20 1 20
3 30 1 10
3 30 2 20
4 40 2 10
4 40 3 20
4 40 4 4这样的sql怎么写?
甲乙丙丁四个人去商店买东西,结果每个人在不同的商店里看中了一件不同的商品,四件商品ABCD的价格依次是40,30,20,10元
而凑巧他们4个人身上分别只带有10,20,30,40元
假设付款必须按照人的顺序来付款,也就是先由甲来支付10元,再由乙支付20元,依次类推。
那么买这4件商品的最后支付情况是这样的:
甲为A支付10元
乙为A支付20元
丙为A支付10元
丙为B支付20元
丁为B支付10元
丁为C支付20元
丁为D支付10元希望的sql就是能算出支付的情况,如上所述。
不知道一条sql能不能实现这个?
我苦恼时候的家
多谢兄弟们捧场!
SELECT fid,fee,sum(fee)over(ORDER BY fid)sum_fee FROM A)
,t2 AS(
SELECT cid,cmoney,sum(cmoney)over(order BY cid)sum_cmoney FROM b)
,t3 AS(
SELECT t1.*,t.*,
LAG(t1.sum_fee)OVER(ORDER BY fid,cid) lg_sum_fee,
LAG(t.sum_cmoney)OVER(ORDER BY fid,cid) lg_sum_cmoney
FROM t1,t2 t
WHERE t1.sum_fee-t1.fee<t.sum_cmoney(+)
AND NOT EXISTS(
SELECT 1 FROM t2 WHERE cid<t.cid AND sum_cmoney>=t1.sum_fee)
)
SELECT fid,fee,cid,
CASE WHEN sum_cmoney>=sum_fee AND lg_sum_cmoney<lg_sum_fee
THEN sum_fee-lg_sum_cmoney
WHEN sum_cmoney>=sum_fee THEN fee
ELSE sum_cmoney-GREATEST(sum_fee-fee,sum_cmoney-cmoney) END cpayfee
FROM t3
ORDER BY fid,cid;
WITH t1 AS(
SELECT fid,fee,sum(fee)over(ORDER BY fid)sum_fee FROM A)
,t2 AS(
SELECT cid,cmoney,sum(cmoney)over(order BY cid)sum_cmoney FROM b)
,t3 AS(
SELECT t1.*,t.*,
LAG(t1.sum_fee)OVER(ORDER BY fid,cid) lg_sum_fee,
LAG(t.sum_cmoney)OVER(ORDER BY fid,cid) lg_sum_cmoney
FROM t1,t2 t
WHERE t1.sum_fee-t1.fee<t.sum_cmoney(+)
AND t1.sum_fee>t.sum_cmoney(+)-t.cmoney(+)
)
SELECT fid,fee,cid,
CASE WHEN sum_cmoney>=sum_fee AND lg_sum_cmoney<lg_sum_fee
THEN sum_fee-lg_sum_cmoney
WHEN sum_cmoney>=sum_fee THEN fee
ELSE sum_cmoney-GREATEST(sum_fee-fee,sum_cmoney-cmoney) END cpayfee
FROM t3
ORDER BY fid,cid;