一个人去商店购物,他看中了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语句输出上面这个明细结果集...,在线等,3Q#89
表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语句输出上面这个明细结果集...,在线等,3Q#89
你是说那个2周内不用再登陆的吗,根本不起作用的
我电脑的cookie是启用的
create table TGood
(
id varchar(5),
fee money
)
insert into TGood
select 'A',40
union select 'B',30
union select 'C',20
union select 'D',10
--select * from TGood
create table TCard
(
cid int ,
cmoney money,
cshop varchar(100)
)insert into TCard
select 1,20,'A'
union select 2,25,'A,B'
union select 3,100,'A,B,C,D'create table Temp
(
id varchar(2),
fee money,
cid int,
cpaymoney money
)
declare @cpaymoney money
declare @neededmoney money
--select * from TCard
declare @ID varchar,@Fee money
declare GoodsC cursor for select id,fee from TGood
open GoodsC
fetch GoodsC into @ID,@Fee
while @@fetch_status=0
begin
set @neededmoney=@Fee
set @cpaymoney=0
--二级游标
declare @cid int,@cmoney money
declare CardsC cursor for select cid,cmoney from TCard where charindex(@ID,cshop)>0 order by cid
open CardsC
fetch CardsC into @cid,@cmoney
while @@fetch_status=0 and @neededmoney!=0
begin
--卡余额
set @cmoney=@cmoney-isnull((select sum(cpaymoney) from Temp where cid=@cid),0)
--商品已经扣减的金额
set @cpaymoney=isnull((select sum(cpaymoney) from Temp where id=@id),0)
--还需扣减的金额
set @neededmoney=@Fee-@cpaymoney
if(@cmoney>0)
begin
--插入一条记录
insert into Temp
select @ID,@Fee,@cid,case when @cmoney>@neededmoney then @neededmoney else @cmoney end
end
--如果卡余额>需要金额,则一次扣减完场商品扣减,设置需要金额为0,跳出游标
if(@cmoney>@neededmoney)
set @neededmoney=0
fetch CardsC into @cid,@cmoney
end
close CardsC
deallocate CardsCfetch GoodsC into @ID,@Fee
end
close GoodsC
deallocate GoodsCselect * from Temp order by id,ciddrop table TCard
drop table TGood
drop table Temp
id fee cid cpaymoney
---- --------------------- ----------- ---------------------
A 40.00 1 20.00
A 40.00 2 20.00
B 30.00 2 5.00
B 30.00 3 25.00
C 20.00 3 20.00
D 10.00 3 10.00(6 行受影响)
--drop table #goods
create table #goods(id varchar(2) , fee int ,orderid int )
insert #goods values('A', 40 ,1)
insert #goods values('B', 30 ,2)
insert #goods values('C', 20 ,3)
insert #goods values('D', 10 ,4)
insert #goods values('E',70,5)
--drop table #car
create table #car ( cid int , cmoney int ,cshop varchar(10) ,orderid2 int )
insert #car values(1 ,20, 'A' ,1 )
insert #car values(2 ,25, 'A,B' ,2 )
insert #car values(3, 30, 'C,D' ,3)
insert #car values(4, 50, 'A,B,D' ,4)
-----支付规则:
--1)卡的orderid 从小到大,商品也是从小到大;
--2) 支付的卡必需能够购买该商品
--3)支付的卡必需有余额且商品也没有完全付清款
-- 支付顺序 select * from #pay drop table #pay
select
identity(int,1,1) as payid, id as gid , cid as cid
into #pay
from #goods g,#car c
where CHARINDEX(id ,cshop) > 0
order by g.orderid ,c.orderid2
----取得每笔支付总共累计需支付的金额 drop table #needpay
select payid,SUM(fee) as needpay
into #needpay
from
(
select distinct p.payid ,pg.gid ,g.fee
from #pay p
left join #pay pg on pg.payid <= p.payid
left join #goods g on g.id = pg.gid
) a
group by payid
-------根据卡里的钱,计算到已经累计支付了多少 drop table #havepay
select payid,SUM(cmoney) as havepay
into #havepay
from
(
select distinct p.payid ,c.cmoney
from #pay p
left join #car c on c.cid <= p.cid
) a
group by payid
----删除已经那些已经付完了款,还在交易的支付明细 drop table #payflow
select p.* ,
np.needpay ,hp.havepay ,needpay - havepay as morepay
into #payflow
from #pay p
left join #needpay np on p.payid = np.payid
left join #havepay hp on p.payid = hp.payid
left join #car c on c.cid = p.cid
left join #goods g on g.id = p.gid
---删除已经那些已经付完了款,还在交易的支付明细
delete #payflow
from
(
select gid ,MIN(morepay) as more
from #payflow
where morepay < 0
group by gid
having COUNT(1) > 1
) a
where a.gid = #payflow.gid and a.more = #payflow.morepay
--------------
select a.gid ,g.fee ,a.cid ,
(case
when isnull( b. morepay,0) = 0 and fee <= cmoney then fee
when isnull( b. morepay,0) = 0 and fee > cmoney then cmoney
when cmoney <= ABS( b. morepay) and cmoney <= fee then cmoney
when ABS( b. morepay)<= cmoney and ABS( b. morepay ) <= fee then ABS(b.morepay)
when fee <= cmoney and fee <= ABS( b. morepay ) then fee
end ) as pay
from #payflow a
left join
(
select pf.payid ,max(pf2.payid ) as payid2
from #payflow pf
left join #payflow pf2 on pf2.payid < pf.payid
group by pf.payid
) t on a.payid = t.payid
left join #payflow b on t.payid2 = b.payid
inner join #car c on a.cid = c.cid
inner join #goods g on a.gid = g.id