序号 数值
1 16
2 16
3 5
4 4
5 15
.... ....
957 18
958 3
959 13
960 5
961 11
962 12
963 15得到新列“差值”,即本行值-上行值的绝对值:序号 数值 差值
1 16 null
2 16 0
3 5 11
4 4 1
5 15 11
.... .... ...
957 18
958 3 15
959 13 10
960 5 8
961 11 6
962 12 1
963 15 3谢谢
1 16
2 16
3 5
4 4
5 15
.... ....
957 18
958 3
959 13
960 5
961 11
962 12
963 15得到新列“差值”,即本行值-上行值的绝对值:序号 数值 差值
1 16 null
2 16 0
3 5 11
4 4 1
5 15 11
.... .... ...
957 18
958 3 15
959 13 10
960 5 8
961 11 6
962 12 1
963 15 3谢谢
差值=abs(数值-(select top 1 数值 from tb where 序号<t.序号 order by 序号 desc))
from tb t
create table tb
(
序号 int,
数值 int
)
insert into tb
select 1, 16
union all
select 2, 16
union all
select 3, 5
union all
select 4, 4
union all
select 5, 15SELECT 序号, 数值, 差值 = ABS(数值 - ISNULL((SELECT TOP 1 数值 FROM tb WHERE 序号 = (t.序号 - 1)), 0))
FROM tb t
(SELECT number = ROW_NUMBER() OVER(ORDER BY 序号), * FROM tb) A
LEFT JOIN
(SELECT number = ROW_NUMBER() OVER(ORDER BY 序号), * FROM tb) B
ON A.number - 1 = B.number
insert into tb
select 1, 16
union all
select 2, 16
union all
select 3, 5
union all
select 4, 4
union all
select 5, 15
goselect t.* , 差值 = abs(数值 - (select top 1 数值 from tb where 序号 < t.序号 order by 序号 desc)) from tb tdrop table tb/*
序号 数值 差值
----------- ----------- -----------
1 16 NULL
2 16 0
3 5 11
4 4 1
5 15 11(所影响的行数为 5 行)
*/