这样的sql怎么写

Question

一个人去商店购物，他看中了ABCD四件商品，价格如下:
表t1
id  fee
A   40
B   30
C   20
D   10同时他带有3张卡，假设编号为1(有20块钱)的卡只能购买A商品，编号为2(有25块钱)的卡只能购买A、B商品，编号为3(有100块钱)的卡可以购买所有的商品，如下:
表t2
cid   cmoney   cshop
1     20        A
2     25        A,B
3     100       A,B,C,D假设目前的规定是这样的：支付的顺序按照编号(cid)排序
那么最后的付款明细是这样的：
id  fee cid cpaymoney
A   40  1    20
A   40  2    20
B   30  2    5
B   30  3    25
C   20  3    20
D   10  3    10请问获取明细的sql怎么写？
要求是用oracle的分析函数，不要用curor或者循环。

Answer 1

create table t1 (id char(1),fee number(4));
insert into t1 values('A','40');
insert into t1 values('B','30');
insert into t1 values('C','20');
insert into t1 values('D','10');
commit;create table t2 (cid number(3),cmoney number(4),cshop varchar2(10));
insert into t2 values(1,20,'A');
insert into t2 values(2,25,'A,B');
insert into t2 values(3,100,'A,B,C,D');
commit;with c1 as(
select id,fee,sum(fee) over (order by id) sfee from t1
),
c2 as(
select c1.id,c1.fee,t2.cid,t2.cmoney, c1.sfee
from c1, t2 
where instr(t2.cshop,c1.id)>0
order by c1.id,t2.cid
),
c3 as(
select id,fee,cid,cmoney,sfee,
sfee-cmoney-(select nvl(sum(distinct cmoney),0) from c2 where id<=c.id and cid<c.cid) diff
from c2 c
),
c4 as(
select id,fee,cid,
decode(sign(diff),1,fee-diff,decode(sign(cmoney+diff-fee),-1,cmoney+diff,fee)) cpaymoney
from c3
)
select * from c4 where cpaymoney>0;
/*
A  40                     1                      20                     
A  40                     2                      20                     
B  30                     2                      5                      
B  30                     3                      25                     
C  20                     3                      20                     
D  10                     3                      10                     */drop table t1 purge;
drop table t2 purge;

Answer 2

-- 上面的有问题，不具有一般性。希望这个可以：〉
create table t1 (id char(1),fee number(4));
insert into t1 values('A','40');
insert into t1 values('B','30');
insert into t1 values('C','20');
insert into t1 values('D','10');
commit;create table t2 (cid number(3),cmoney number(4),cshop varchar2(10));
insert into t2 values(1,20,'A');
insert into t2 values(2,25,'A,B');
insert into t2 values(3,100,'A,B,C,D');
commit;with c1 as(
select id,fee,sum(fee) over (order by id) sfee from t1
),
c2 as(
select c1.id,c1.fee,t2.cid,t2.cmoney, c1.sfee
from c1, t2 
where instr(t2.cshop,c1.id)>0
order by c1.id,t2.cid
),
c3 as(
select id,fee,cid,cmoney,sfee,rownum rn,
(select nvl(sum(cmoney),0) from t2 where cid in (select cid from c2 where id<=c.id and cid<c.cid)) smoney
from c2 c
),
c4 as(
select id,fee,cid,cmoney,sfee,
(case when smoney>=(select nvl(sum(fee),0) from t1 where id in (select id from c3 where rn<c.rn)) 
 then (select nvl(sum(fee),0) from t1 where id in (select id from c3 where rn<c.rn)) +cmoney else smoney+cmoney end) smoney
from c3 c
),
c5 as(
select id,fee,cid,cmoney,sfee,smoney,sfee-smoney remain from c4
)
select id,fee,cid,cmoney,sfee,smoney,remain,
decode(sign(remain),0,fee,1,fee-remain,decode(sign(cmoney+remain-fee),-1,cmoney+remain,fee)) cpaymoney
from c5;
drop table t1 purge;
drop table t2 purge;

Answer 3

to xman_78tom:
c3 as(
select id,fee,cid,cmoney,sfee,rownum rn,
(select nvl(sum(cmoney),0) from t2 where cid in (select cid from c2 where id<=c.id and cid<c.cid)) smoney
from c2 c
),这个语句在面对大批量的数据的时候，会很慢哦！
因为在我的场景里c2有200w条数据！希望能得到你的正解！