62,244
社区成员




create table testDemo
(
总 int ,
剩 int,
dates int ,
)
insert into testDemo values(100,80,1)
insert into testDemo values(80,60,2)
insert into testDemo values(60,40,3)
insert into testDemo values(40,20,4)
select * from testDemo
select case testDemo.dates when a.dates+1 then a.剩 end as money11,testDemo.剩,testDemo.dates from testDemo,
(select 剩,dates from testDemo) as a where testDemo.dates=a.dates+1
drop table testDemo
declare @tb table(id int,[name] varchar(20),[money] float, money2 float, money3 float, [month] int)
insert into @tb
select 1,'张三',10,5,5,1 union all
select 2,'张三',5,5,2,2 union all
select 3,'张三',10,5,1,3 union all
select 4,'张三',5,5,0,4 union all
select 5,'李四',10,5,5,1 union all
select 6,'李四',5,5,0,2 union all
select 7,'李四',10,5,5,3 union all
select 8,'李四',5,5,0,4
select id,[name],[money]=isnull([money]+(select money3 from @tb where [name]=a.[name] and [month]=a.[month]-1),[money])
,money2,money3,[month] from @tb a
/*
1 张三 10 5 5 1
2 张三 10 5 2 2
3 张三 12 5 1 3
4 张三 6 5 0 4
5 李四 10 5 5 1
6 李四 10 5 0 2
7 李四 10 5 5 3
8 李四 10 5 0 4
*/
declare @tb table(id int,[name] varchar(20),[money] float, money2 float, money3 float, [month] int)
insert into @tb
select 1,'张三',10,5,5,1 union all
select 2,'张三',5,5,2,2 union all
select 3,'张三',10,5,1,3 union all
select 4,'张三',5,5,0,4 union all
select 5,'李四',10,5,5,1 union all
select 6,'李四',5,5,0,2 union all
select 7,'李四',10,5,5,3 union all
select 8,'李四',5,5,0,4
select id,[name],[money]=isnull([money]+(select money3 from @tb where [name]=a.[name] and [month]=a.[month]-1),[money])
,money2,money3,[month] from @tb a
id name total spend remain month
1 张三 10 5 5 2011-1