日期:2014-05-18  浏览次数:20570 次

一个会员卡使用问题
有一数据表存储用户的会员卡信息,有列(主键id,用户Id,剩余的钱,过期时间)

Id UserId RemainingMoney ExpireDate
1 1 3.5 2012-5-1
2 2 3.5 2012-5-1
3 1 4.0 2012-6-2
4 1 5.0 2012-6-1

要求1
输入参数@UserId,扣款金额@Money
按过期时间的顺序,输出满足指定金额@Money的记录,比如输入@UserId=1,@Money=6,则返回id为1和4的记录,因为这两条记录的总额足够支付6,而且比记录3先过期。

要求2
执行扣款操作,比如上面的条件,记录1更新为0,记录4更新为2.5

目前使用游标解决,一条一条的处理,但该操作比较频繁,不知是否有更好的办法?




------解决方案--------------------
SQL code

--没用游标,你可以测试一下

declare @T table 
(Id int,UserId int,RemainingMoney numeric(2,1),ExpireDate datetime)
insert into @T
select 1,1,3.5,'2012-5-1' union all
select 2,2,3.5,'2012-5-1' union all
select 3,1,4.0,'2012-6-2' union all
select 4,1,5.0,'2012-6-1'

--给参数赋值
declare @userid int set @userid=1
declare @money int set @money=4
declare @j datetime
declare @k numeric(2,1)

;with maco as  
( select * from @T where UserId=@userid),
maco1 as
(  
select *,总金额=
(select sum(RemainingMoney) from maco where ExpireDate<=a.ExpireDate) from maco a
)  
select top 1 @j=ExpireDate,@k=总金额-@money from maco1 where 总金额>=@money  order by ExpireDate

update @T set RemainingMoney=@k where ExpireDate=@j and UserId=@userid 
update @T set RemainingMoney=0 where UserId=@userid and ExpireDate<@j  

select * from @T  
/*
Id          UserId      RemainingMoney                          ExpireDate
----------- ----------- --------------------------------------- -----------------------
1           1           0.0                                     2012-05-01 00:00:00.000
2           2           3.5                                     2012-05-01 00:00:00.000
3           1           4.0                                     2012-06-02 00:00:00.000
4           1           4.5                                     2012-06-01 00:00:00.000
*/