有一数据表存储用户的会员卡信息,有列(主键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
[email protected],[email protected]
按过期时间的顺序，[email protected]，[email protected]=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 @Tselect 1,1,3.5,'2012-5-1' union allselect 2,2,3.5,'2012-5-1' union allselect 3,1,4.0,'2012-6-2' union allselect 4,1,5.0,'2012-6-1'--给参数赋值declare @userid int set @userid=1declare @money int set @money=4declare @j datetimedeclare @k numeric(2,1);with maco as ( select * from @T where [email protected]),maco1 as( select *,总金额=(select sum(RemainingMoney) from maco where ExpireDate<=a.ExpireDate) from maco a) select top 1 @j=ExpireDate,@[email protected] from maco1 where 总金额>[email protected] order by ExpireDateupdate @T set [email protected] where [email protected] and [email protected] update @T set RemainingMoney=0 where [email protected] and ExpireDate<@j select * from @T /*Id UserId RemainingMoney ExpireDate----------- ----------- --------------------------------------- -----------------------1 1 0.0 2012-05-01 00:00:00.0002 2 3.5 2012-05-01 00:00:00.0003 1 4.0 2012-06-02 00:00:00.0004 1 4.5 2012-06-01 00:00:00.000*/