更新时间:2023-02-05 10:17:05
假设您有一个排序列——比如 id
——那么您可以在 SQL Server 2012 中执行以下操作:
Assuming you have an ordering column -- say id
-- then you can do the following in SQL Server 2012:
select col,
col - coalesce(lag(col) over (order by id), 0) as diff
from t;
在早期版本的 SQL Server 中,您可以使用相关子查询执行几乎相同的操作:
In earlier versions of SQL Server, you can do almost the same thing using a correlated subquery:
select col,
col - isnull((select top 1 col
from t t2
where t2.id < t.id
order by id desc
), 0)
from t
这使用 isnull()
而不是 coalesce()
因为 SQL Server 中的错误"在使用 coalesce() 时会计算第一个参数两次代码>.
This uses isnull()
instead of coalesce()
because of a "bug" in SQL Server that evaluates the first argument twice when using coalesce()
.
你也可以用 row_number()
做到这一点:
You can also do this with row_number()
:
with cte as (
select col, row_number() over (order by id) as seqnum
from t
)
select t.col, t.col - coalesce(tprev.col, 0) as diff
from cte t left outer join
cte tprev
on t.seqnum = tprev.seqnum + 1;
所有这些都假设您有一些用于指定排序的列.它可能是 id
,或创建日期或其他内容.SQL 表本质上是无序的,因此没有指定排序的列就没有前一行"这样的东西.
All of these assume that you have some column for specifying the ordering. It might be an id
, or a creation date or something else. SQL tables are inherently unordered, so there is no such thing as a "previous row" without a column specifying the ordering.