3 回答
TA贡献1966条经验 获得超4个赞
SELECT rowInt, Value,
COALESCE(
(
SELECT TOP 1 Value
FROM myTable mi
WHERE mi.rowInt > m.rowInt
ORDER BY
rowInt
), 0) - Value AS diff
FROM myTable m
ORDER BY
rowInt
TA贡献1865条经验 获得超7个赞
SQL Server 2012及更高版本支持LAG / LEAD函数来访问上一行或下一行。SQL Server 2005不支持此功能(在SQL2005中,您需要联接或其他方式)。
有关此数据的SQL 2012示例
/* Prepare */
select * into #tmp
from
(
select 2 as rowint, 23 as Value
union select 3, 45
union select 17, 10
union select 9, 0
) x
/* The SQL 2012 query */
select rowInt, Value, LEAD(value) over (order by rowInt) - Value
from #tmp
LEAD(value)将返回“ over”子句中给定顺序的下一行的值。
- 3 回答
- 0 关注
- 383 浏览
添加回答
举报