SQL 查找前一行和当前行之间的差异 [英] SQL Find difference between previous and current row

问题描述

我试图找出当前行和前一行之间的差异.但是，我收到以下错误消息:

I am trying to find the difference between the current row and the previous row. However, I am getting the following error message:

无法绑定多部分标识符tableName".

The multi-part identifier "tableName" could not be bound.

不确定如何修复错误.

谢谢！

输出应如下所示:

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 查找前一行和当前行之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！