本文介绍了SQL 查找前一行和当前行之间的差异的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图找出当前行和前一行之间的差异.但是,我收到以下错误消息:
I am trying to find the difference between the current row and the previous row. However, I am getting the following error message:
无法绑定多部分标识符tableName".
不确定如何修复错误.
谢谢!
输出应如下所示:
columnOfNumbers Difference
1 NULL
2 1
3 1
10 7
12 2
.... ....
代码:
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
推荐答案
参见 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
这篇关于SQL 查找前一行和当前行之间的差异的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!