SQL 查找前一行和当前行之间的差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23200189/
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
SQL Find difference between previous and current row
提问by ChaseHardin
I am trying to find the difference between the current row and the previous row. However, I am getting the following error message:
我试图找到当前行和上一行之间的差异。但是，我收到以下错误消息：
The multi-part identifier "tableName" could not be bound.
无法绑定多部分标识符“tableName”。
Not sure how to fix the error.
不知道如何修复错误。
Thanks!
谢谢！
Output should look like the following:
输出应如下所示：
columnOfNumbers Difference
1 NULL
2 1
3 1
10 7
12 2
.... ....
Code:
代码：
USE DATABASE;
WITH CTE AS
(SELECT
ROW_NUMBER() OVER (PARTITION BY tableName ORDER BY columnOfNumbers) ROW,
columnOfNumbers
FROM tableName)
SELECT
a.columnOfNumbers
FROM
CTE a
LEFT JOIN CTE b
ON a.columnOfNumbers = b.columnOfNumbers AND a.ROW = b.ROW + 1
回答by Karl Kieninger
If you in SQL Server 2012+ You can use LAG.
如果您在 SQL Server 2012+ 中，您可以使用 LAG。
SELECT columnOfNumbers
,columnOfNumbers - LAG(columnOfNumbers, 1) OVER (ORDER BY columnOfNumbers)
FROM tableName
Note: The optional third parameter of LAG is:
注意：LAG 可选的第三个参数是：
default
The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned. default can be a column, subquery, or other expression, but it cannot be an analytic function. default must be type-compatible with scalar_expression.
默认
当偏移处的 scalar_expression 为 NULL 时要返回的值。如果未指定默认值，则返回 NULL。default 可以是列、子查询或其他表达式，但不能是分析函数。default 必须与 scalar_expression 类型兼容。
回答by Tom
See sqlFiddle
;WITH tblDifference AS
(
SELECT ROW_NUMBER() OVER(ORDER BY id) AS RowNumber, columnOfNumbers
FROM tableName
)
SELECT cur.columnOfNumbers, cur.columnOfNumbers - previous.columnOfNumbers
FROM tblDifference cur
LEFT OUTER JOIN tblDifference previous
ON cur.RowNumber = previous.RowNumber + 1
回答by Gordon Linoff
I don't think you need the partition bystatement:
我认为你不需要partition by声明：
WITH CTE AS (
SELECT ROW_NUMBER() OVER (ORDER BY columnOfNumbers) as ROW,
columnOfNumbers
FROM tableName
)
SELECT a.columnOfNumbers, a.columnOfNumbers - b.columnOfNumbers
FROM CTE a LEFT JOIN
CTE b
ON a.ROW = b.ROW + 1;
If you do need it, you should put in a column name as opposed to a table name.
如果您确实需要它，您应该输入一个列名而不是一个表名。
