select *,(select sum(case types when 1 then moneys else -money end)nowmoneys from a a1 where a1.ids<=a.ids)nowmoneys from a
Select *,(select sum(case when types = 1 then moneys else -moneys end) from 表名where ids<=a.ids )as nowmoneys from 表名 a
CREATE TABLE [dbo].[a] ( [ids] [int] IDENTITY (1, 1) NOT NULL , [type] [int] NOT NULL , [money] [money] NOT NULL ) ON [PRIMARY] GO select *,(select sum(type*money) from a as b where b.ids<=a.ids) as nowmoneys from a ids type money nowmoneys ----------- ----------- --------------------- --------------------- 1 1 100.0000 100.0000 2 -1 30.0000 70.0000 3 1 10.0000 80.0000
select *,nowmoneys=(select sum(moneys*case when types=1 then 1 else -1 end) from a where ids<=a.ids) from a aa
[ids] [int] IDENTITY (1, 1) NOT NULL ,
[type] [int] NOT NULL ,
[money] [money] NOT NULL
) ON [PRIMARY]
GO
select *,(select sum(type*money) from a as b where b.ids<=a.ids) as nowmoneys from a ids type money nowmoneys
----------- ----------- --------------------- ---------------------
1 1 100.0000 100.0000
2 -1 30.0000 70.0000
3 1 10.0000 80.0000
from a aa