SQL 字段 = 其他字段减去另一行

Question

表有 2 列：[nr] 和 [diff]

diff 为空（到目前为止 - 需要填充）

nr 有数字：

 1
 2
 45
 677
 43523452

在 diff 列上，我需要添加对之间的差异，

 1 | 0
 2 | 1
 45 | 43
 677 | 632
 43523452 | 43522775

所以基本上类似于：

 update tbl set diff = @nr - @nrold where nr = @nr

但我不想要接下来使用 fetch，因为它不酷，而且速度很慢（100.000）条记录，

我怎样才能通过一次更新来做到这一点？

Answer 1

CREATE TABLE #T(nr INT,diff INT)

INSERT INTO #T (nr) SELECT 1 
              UNION SELECT 2 
              UNION SELECT 45 
              UNION SELECT 677 
              UNION SELECT 43523452

;WITH cte AS
(
SELECT nr,diff, ROW_NUMBER() OVER (ORDER BY nr) RN
FROM #T
)
UPDATE c1
SET diff = ISNULL(c1.nr - c2.nr,0)
FROM cte c1
LEFT OUTER JOIN cte c2 ON c2.RN+1= c1.RN

SELECT nr,diff FROM #T

DROP TABLE #T

Answer 2

看看这样的东西（完整的例子）

DECLARE @Table TABLE(
        nr INT,
        diff INT
)

INSERT INTO @Table (nr) SELECT 1 UNION ALL
SELECT 2  UNION ALL
SELECT 45  UNION ALL
SELECT 677  UNION ALL
SELECT 43523452 

;WITH Vals AS (
        SELECT  nr,
                diff,
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) xID
        FROM    @Table
)
UPDATE  c
SET     diff = c.nr - ISNULL(p.nr, 0)
FROM    Vals c LEFT JOIN
        Vals p  ON  c.xID = p.xID + 1


SELECT  *
FROM    @Table

Answer 3

试试这个 -

update tablename
set diff = cast(nr as INT) - cast((select nr from tablename where diff is not null and nr = a.nr) as INT)
from tablename a
where diff is null

假设表中只有 nr old 的一个旧行。否则子查询将返回多个值