a b c
002 2002-01-30 1000
002 2002-02-28 1500
002 2003-04-30 2800
002 2003-05-30 3000
003 2003-04-30 2800
003 2003-05-30 3000
要得到这样的结果集
a b c d
002 2002-01-30 1000 1000
002 2002-02-28 1500 500
002 2003-04-30 2800 2800
002 2003-05-30 3000 200
003 2003-04-30 2800 2800
003 2003-05-30 3000 200
不同年份相邻的月份才能相减我用的update语句不对 想用循环怎么写
------最佳解决方案--------------------
-- 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(a varchar(8), b date, c int, d int)
insert into #(a,b,c)
select '002', '2002-01-30', 1000 union all
select '002', '2002-02-28', 1500 union all
select '002', '2003-04-30', 2800 union all
select '002', '2003-05-30', 3000 union all
select '003', '2003-04-30', 2800 union all
select '003', '2003-05-30', 3000
update a set a.d = a.c-isnull(b.c,0) from # a left join # b on a.a=b.a and year(a.b)=year(b.b) and month(a.b)=month(b.b)+1
select * from #
/*
a b c d
-------- ---------- ----------- -----------
002 2002-01-30 1000 1000
002 2002-02-28 1500 500
002 2003-04-30 2800 2800
002 2003-05-30 3000 200
003 2003-04-30 2800 2800