有没有办法访问“上一行"?SELECT 语句中的值? [英] Is there a way to access the "previous row" value in a SELECT statement?

问题描述

我需要计算表格两行之间一列的差异.有什么办法可以直接在 SQL 中执行此操作吗?我使用的是 Microsoft SQL Server 2008.

I need to calculate the difference of a column between two lines of a table. Is there any way I can do this directly in SQL? I'm using Microsoft SQL Server 2008.

我正在寻找这样的东西:

I'm looking for something like this:

SELECT value - (previous.value) FROM table

想象上一个"变量引用了最新选择的行.当然，使用这样的选择，我最终会在具有 n 行的表中选择 n-1 行，这可能不是，实际上正是我需要的.

Imagining that the "previous" variable reference the latest selected row. Of course with a select like that I will end up with n-1 rows selected in a table with n rows, that's not a probably, actually is exactly what I need.

这在某种程度上可能吗?

Is that possible in some way?

推荐答案

SQL 没有内置的顺序概念，因此您需要按某些列进行排序才能使其有意义.像这样:

SQL has no built in notion of order, so you need to order by some column for this to be meaningful. Something like this:

select t1.value - t2.value from table t1, table t2 
where t1.primaryKey = t2.primaryKey - 1

如果您知道如何排序，但不知道如何获取给定当前值的前一个值(例如，您想按字母顺序排序)，那么我不知道在标准 SQL 中可以这样做的方法，但大多数 SQL实现将有扩展来做到这一点.

If you know how to order things but not how to get the previous value given the current one (EG, you want to order alphabetically) then I don't know of a way to do that in standard SQL, but most SQL implementations will have extensions to do it.

如果您可以对行进行排序以使每个行都不同，则这是一种适用于 SQL Server 的方法:

Here is a way for SQL server that works if you can order rows such that each one is distinct:

select  rank() OVER (ORDER BY id) as 'Rank', value into temp1 from t

select t1.value - t2.value from temp1 t1, temp1 t2 
where t1.Rank = t2.Rank - 1

drop table temp1

如果您需要打破平局，您可以根据需要向 ORDER BY 添加任意数量的列.

If you need to break ties, you can add as many columns as necessary to the ORDER BY.

这篇关于有没有办法访问“上一行"?SELECT 语句中的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！