SQL 有没有办法在 SELECT 语句中访问“上一行”值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/710212/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to access the "previous row" value in a SELECT statement?
提问by Edwin Jarvis
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.
我需要计算表格两行之间一列的差异。有什么办法可以直接在 SQL 中执行此操作吗？我正在使用 Microsoft SQL Server 2008。
I'm looking for something like this:
我正在寻找这样的东西：
SELECT value - (previous.value) FROM table
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.
想象“上一个”变量引用了最新的选定行。当然，对于这样的选择，我最终会在具有 n 行的表中选择 n-1 行，这可能不是，实际上正是我需要的。
Is that possible in some way?
这在某种程度上可能吗？
采纳答案by RossFabricant
SQL has no built in notion of order, so you need to order by some column for this to be meaningful. Something like this:
SQL 没有内置的顺序概念，因此您需要按某些列进行排序才能使其有意义。像这样的东西：
select t1.value - t2.value from table t1, table t2
where t1.primaryKey = t2.primaryKey - 1
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 中可以做到这一点，但大多数 SQL 实现都有扩展来做到这一点。
Here is a way for SQL server that works if you can order rows such that each one is distinct:
如果您可以对行进行排序以使每个行都不同，则这是一种适用于 SQL Server 的方法：
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
If you need to break ties, you can add as many columns as necessary to the ORDER BY.
如果您需要打破平局，您可以根据需要向 ORDER BY 添加尽可能多的列。
回答by Hans Ginzel
回答by Quassnoi
Oracle, PostgreSQL, SQL Server and many more RDBMS engines have analytic functions called LAGand LEADthat do this very thing.
Oracle、PostgreSQL、SQL Server 和更多 RDBMS 引擎都调用了分析函数LAG，LEAD这些函数就是做这件事的。
In SQL Server prior to 2012 you'd need to do the following:
在 2012 年之前的 SQL Server 中，您需要执行以下操作：
SELECT value - (
SELECT TOP 1 value
FROM mytable m2
WHERE m2.col1 < m1.col1 OR (m2.col1 = m1.col1 AND m2.pk < m1.pk)
ORDER BY
col1, pk
)
FROM mytable m1
ORDER BY
col1, pk
, where COL1is the column you are ordering by.
，COL1您订购的列在哪里。
Having an index on (COL1, PK)will greatly improve this query.
建立索引(COL1, PK)将大大改善此查询。
回答by Jeremy Stein
WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY columns_to_order_by),
value
FROM table
)
SELECT
curr.value - prev.value
FROM CTE cur
INNER JOIN CTE prev on prev.rownum = cur.rownum - 1
回答by Joel Coehoorn
LEFT JOIN the table to itself, with the join condition worked out so the row matched in the joined version of the table is one row previous, for your particular definition of "previous".
LEFT JOIN 将表与自身连接起来，并计算出连接条件，因此在表的连接版本中匹配的行是前一行，用于您对“上一个”的特定定义。
Update: At first I was thinking you would want to keep all rows, with NULLs for the condition where there was no previous row. Reading it again you just want that rows culled, so you should an inner join rather than a left join.
更新：起初我想你会想要保留所有行，在没有前一行的情况下使用 NULL。再次阅读它，您只想剔除这些行，因此您应该使用内部连接而不是左连接。
Update:
更新：
Newer versions of Sql Server also have the LAG and LEAD Windowing functions that can be used for this, too.
较新版本的 Sql Server 还具有 LAG 和 LEAD 窗口功能，也可用于此目的。
回答by user1920851
select t2.col from (
select col,MAX(ID) id from
(
select ROW_NUMBER() over(PARTITION by col order by col) id ,col from testtab t1) as t1
group by col) as t2
回答by HLGEM
The selected answer will only work if there are no gaps in the sequence. However if you are using an autogenerated id, there are likely to be gaps in the sequence due to inserts that were rolled back.
所选答案仅在序列中没有间隙时才有效。但是，如果您使用的是自动生成的 id，由于插入回滚，序列中可能会出现间隙。
This method should work if you have gaps
如果您有差距，此方法应该有效
declare @temp (value int, primaryKey int, tempid int identity)
insert value, primarykey from mytable order by primarykey
select t1.value - t2.value from @temp t1
join @temp t2
on t1.tempid = t2.tempid - 1
