一个人去商店购物,他看中了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或者循环。
另外请注意性能问题,就是说要考虑表t1,t2如果都是100条数据的情况。
表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或者循环。
另外请注意性能问题,就是说要考虑表t1,t2如果都是100条数据的情况。
with tmp as(
select * from (select id,fee,sum(FEE)over( order by ID) as remain from xwj_1 )a,
(select cid,cmoney ,sum(cmoney)over( order by cid) as spend,cshop from xwj_2 )b
where INSTR(b.cshop,a.id,1,1) >0
)select id,fee,cid,
case when prev_sal >=0 and remain>=spend then cmoney
when prev_sal >=0 and remain<spend then prev_sal
when prev_sal <0 and abs(prev_sal)>=fee then fee
when prev_sal <0 and abs(prev_sal)<fee then abs(prev_sal)
end paymoney
from
(select z.*,
LAG(remain-spend, 1, 0) OVER (ORDER BY cid,id) AS prev_sal
from
(
select id,fee,remain,cid,cmoney,spend,cshop from tmp where remain-spend>0
union all
select id,fee,remain,cid,cmoney,spend,cshop
from
(select tmp.*,remain-spend,row_number()over(partition by id order by cid)rn from tmp where remain-spend<0)
where rn=1
)z order by cid,id
)y
with t as(
select chr(64+level) id, (5-level)*10 fee from dual connect by level<=4
),
t1 as(
select 1 cid,20 cm,'A' cs from dual
union all
select 2, 25,'A,B'from dual
union all
select 3,100,'A,B,C,D' from dual
),
t2 as(
select id,fee,feeunit,rownum rn from(
select id,fee,5 feeunit from t,(select level l from dual connect by level<=20)
where l<=fee/5
order by id)
),
t3 as(
select cid,feeunit,rownum rn from(
select cid,5 feeunit from t1,(select level l from dual connect by level<=20)
where l<=cm/5
order by cid)
)
select id,fee,cid,sum(t2.feeunit) cpaymoney from t2,t3 where t2.rn=t3.rn
group by id,cid,fee;结果:
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
ITPUB上有人给出了,但是我看还是不行!
楼主,单纯的SQL来实现,确实有点难度哦考虑下其它方法吧,如procedure里写PL/SQL实现