sql server 怎么循环求相邻值的差
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 
 
003      2003-05-30 3000        200
*/

[其他解释]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] varchar(3),[b] datetime,[c] int)
insert [tb]
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
go

select t.*,
  d=c-isnull((select c from tb where a=t.a and datediff(mm,b,t.b)=1),0)
from tb t

/**
a    b                       c           d
---- ----------------------- ----------- -----------
002  2002-01-30 00:00:00.000 1000        1000
002  2002-02-28 00:00:00.000 1500        500
002  2003-04-30 00:00:00.000 2800        2800
002  2003-05-30 00:00:00.000 3000        200
003  2003-04-30 00:00:00.000 2800        2800
003  2003-05-30 00:00:00.000 3000        200

(6 行受影响)
**/

[其他解释]
大侠们，你们真快高手
[其他解释]
高手无处不在。。。
[其他解释]
确实，我还想问下怎么用while 循环实现 用declare
[其他解释]

引用:

SQL code?



12345678910111213141516171819202122232425262728

if object_id('[tb]') is not null drop table [tb] go create table [tb]([a] varchar(3),[b] datetime,[c] int) insert [tb] select '002',……

艹，这是什么
[其他解释]

引用:

SQL code?



1234567891011121314151617181920212223

-- 测试数据：# 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……

我是菜鸟，想问一下a.c,a.b,这些是什么意思，这些不是都是列名来的吗？怎么可以这样用
[其他解释]
回复楼上
update x set x.d = x.c-isnull(y.c,0) from # as x left join # as y on x.a=y.a and year(x.b)=year(y.b) and month(x.b)=month(y.b)+1

understand?
[其他解释]
很好，很强大