你这个有顺序的啊。给你价格排序。不然你数据里面的第二行 第三行互换。结果也不一样啊
create table tb (GroupID varchar(4) ,EveryPrice int ,TotolPrice int ,id int )
insert into tb
select 'A','20','100' ,1union all
select 'B','10','500',1 union all
select 'B','30','500',2 union all
select 'C','30','400',1 union all
select 'C','20','400' ,2 union all
select 'C','600','400',3
go
with cte as
(select * ,TotolPrice-EveryPrice as leave from tb where id=1
union all
select a.GroupID,a.EveryPrice,a.TotolPrice,
a.id,b.leave-a.EveryPrice as leave from tb as a join cte as b on
a.id=b.id+1 and a.GroupID=b.GroupID)
select * from cte
order by GroupID,id
create table tb (GroupID varchar(4) ,EveryPrice int ,TotolPrice int ,id int )
insert into tb
select 'A','20','100' ,1union all
select 'B','10','500',1 union all
select 'B','30','500',2 union all
select 'C','30','400',1 union all
select 'C','20','400' ,2 union all
select 'C','600','400',3
go
with cte as
(select * ,TotolPrice-EveryPrice as leave from tb where id=1
union all
select a.GroupID,a.EveryPrice,a.TotolPrice,
a.id,b.leave-a.EveryPrice as leave from tb as a join cte as b on
a.id=b.id+1 and a.GroupID=b.GroupID)
select * from cte
order by GroupID,id
GroupID EveryPrice TotolPrice id leave
------- ----------- ----------- ----------- -----------
A 20 100 1 80
B 10 500 1 490
B 30 500 2 460
C 30 400 1 370
C 20 400 2 350
C 600 400 3 -250
(
select *,ROW_NUMBER() over(partition by GroupID order by GroupID ) as rId from tb11
)
select GroupID,EveryPrice,TotolPrice,
(TotolPrice-EveryPrice-isnull((select SUM(EveryPrice)
from cte where GroupID=aa.GroupID and rId<aa.rID ),0)) as leave from cte as aa
oracle的写法,不知道这边能不能直接用
select GroupID,EveryPrice,TotolPrice,TotolPrice-sum(EveryPrice)over(partition by GroupID order by GroupID) Leave
from tb
递减 貌似要2012的SQL 才行。08的不行。