create table tmp ( id int null, [money] money null, state int null ) insert into tmp select 1,22.2,0 union all select 5,20,0 union all select 6,35,0 union all select 7,30,1 union all select 8,50,1 union all select 12,30,0 union all select 16,20,1create procedure substract @state int as begin select d.aid,c.amoney,c.sub from (select c.aid,MIN(c.bid) as cbid from ( select a.money as amoney,a.id as aid,b.id as bid,b.money-a.money as sub from (select ID,[money],state from tmp where state=@state) a, (select ID,[money],state from tmp where state=@state) b) c where c.bid>c.aid group by c.aid) d, (select a.money as amoney,a.id as aid,b.id as bid,b.money-a.money as sub from (select ID,[money],state from tmp where state=@state) a, (select ID,[money],state from tmp where state=@state) b) c where c.aid=d.aid and c.bid=d.cbid end--结果 select * from tmp where state=0 exec substract 0 id money state ----------- --------------------- ----------- 1 22.20 0 5 20.00 0 6 35.00 0 12 30.00 0(4 行受影响)aid amoney sub ----------- --------------------- --------------------- 1 22.20 -2.20 5 20.00 15.00 6 35.00 -5.00(3 行受影响)
create table tmp
(
id int null,
[money] money null,
state int null
)
insert into tmp
select 1,22.2,0
union all select 5,20,0
union all select 6,35,0
union all select 7,30,1
union all select 8,50,1
union all select 12,30,0
union all select 16,20,1create procedure substract
@state int
as
begin
select d.aid,c.amoney,c.sub from
(select c.aid,MIN(c.bid) as cbid from (
select a.money as amoney,a.id as aid,b.id as bid,b.money-a.money as sub from
(select ID,[money],state from tmp where state=@state) a,
(select ID,[money],state from tmp where state=@state) b) c
where c.bid>c.aid
group by c.aid) d,
(select a.money as amoney,a.id as aid,b.id as bid,b.money-a.money as sub from
(select ID,[money],state from tmp where state=@state) a,
(select ID,[money],state from tmp where state=@state) b) c
where c.aid=d.aid and c.bid=d.cbid
end--结果
select * from tmp where state=0
exec substract 0
id money state
----------- --------------------- -----------
1 22.20 0
5 20.00 0
6 35.00 0
12 30.00 0(4 行受影响)aid amoney sub
----------- --------------------- ---------------------
1 22.20 -2.20
5 20.00 15.00
6 35.00 -5.00(3 行受影响)