求一个sql 语句《高手请进22》

Question

declare @total_reg table(id int,total_num int,total_date datetime)
insert into @total_reg select 1,30000,'2006-1-1'
insert into @total_reg select 2,35000,'2006-1-2'
insert into @total_reg select 3,38000,'2006-1-3'select
    a.id,
    total_num=(select total_num from @total_reg where datediff(dd,a.total_date,total_date)=1)-a.total_num,
    a.total_date
from
    @total_reg a
where
    exists(select 1 from @total_reg where datediff(dd,a.total_date,total_date)=1)/*
id          total_num   total_date                                             
----------- ----------- ------------------------------------------------------ 
1           5000        2006-01-01 00:00:00.000
2           3000        2006-01-02 00:00:00.000
*/

Answer 1

declare @t table (id int,total_num int,total_date datetime)
insert @t
select 1,30000,'2006-1-1' union all
select 2,35000,'2006-1-2' union all
select 3,38000,'2006-1-3'select id,(select total_num from @t where id = a.id + 1 ) - total_num  as total_num,
total_date 
from @t a 
where 
exists(select 1 from @t where datediff(dd,a.total_date,total_date)=1)/*id          total_num   total_date                                             
----------- ----------- ------------------------------------------------------ 
1           5000        2006-01-01 00:00:00.000
2           3000        2006-01-02 00:00:00.000*/