如何在sql中减去前一行?

2022-01-20 00:00:00 sql-server vb6

如果我想将当前行减去上一行，我应该查询什么.我将在 vb6 中循环使用它.像这样的:

What should I query if I wanted to subtract the current row to the previous row. I will use it on looping in vb6. Something Like this:

Row
1
2
3
4
5

在第一个循环值 1 不会被扣除，因为它没有前一行，这没关系.下一个循环值 2 将被前一行的值 1 减去.依此类推，直到最后一行.

On first loop value 1 will not be deducted because it has no previous row, which is ok. Next loop value 2 will then be deducted by the previous row which is value 1. And so on until the last row.

我怎样才能实现这个例程?通过 SQL 查询或 VB6 代码.任何都可以.

How can I achieve this routine? By SQL query or VB6 code.Any will do.

推荐答案

假设你有一个排序列——比如 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.

相关文章