--测试环境
declare @t table(id int identity(1,1),col1 int)
insert into @t select 1
union all select 3
union all select 14
union all select 15--查询语句
select a.id,b.id,a.col1,b.col1,累计该差额=abs(a.col1-b.col1)
from @t A,@t B
where abs(a.col1-b.col1)>10
and abs(a.id-b.id)=1
and a.id<=b.id
--结果
id id col1 col1 累计该差额
----------- ----------- ----------- ----------- -----------
2 3 3 14 11(所影响的行数为 1 行)
declare @t table(id int identity(1,1),col1 int)
insert into @t select 1
union all select 3
union all select 14
union all select 15--查询语句
select a.id,b.id,a.col1,b.col1,累计该差额=abs(a.col1-b.col1)
from @t A,@t B
where abs(a.col1-b.col1)>10
and abs(a.id-b.id)=1
and a.id<=b.id
--结果
id id col1 col1 累计该差额
----------- ----------- ----------- ----------- -----------
2 3 3 14 11(所影响的行数为 1 行)
(
id int,
col int
)
insert @tb
select 1,1 union
select 2,3 union
select 3,14 union
select 4,15--查询
select sum(差值) as '累计'
from
(
select A.id
,isnull(abs(B.col-A.col),0) as '差值'
from @tb A
left join @tb B on A.id=(select min(id)
from @tb
where id>B.id)
)t
where 差值>10--结果
/*
累计
-----------
11(1 row(s) affected)
*/
insert into @t select 1
union all select 3
union all select 14
union all select 15select identity(int,1,1) id,* into #t from @t
select sum(b.col1-a.col1) '累计差额' from #t a,#t b
where a.id=b.id-1 and b.col1-a.col1>10
drop table #t
(
col int
)
insert @tb
select 1 union
select 3 union
select 14 union
select 15select id=identity(int,1,1),col=col into #t from @tbselect sum(abs(a.col-b.col))
from #t a left join (select id=id-1,col from #t where id>1) b
on a.id=b.id
where abs(a.col-b.col)>10