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

发布于 2024-09-27 17:32:14 字数 411 浏览 0 评论 0原文

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

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

nr 有数字：

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

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

所以基本上类似于：

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

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

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

原文

Table has 2 cols: [nr] and [diff]

diff is empty (so far - need to fill)

nr has numbers:

on the diff column i need to add the differences between pairs

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

so basically something like :

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

but i don't want to use fetch next, because it's not cool, and it's slow (100.000) records

how can I do that with one update?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

暮倦 2024-10-04 17:32:14

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

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

回复收藏 0 原文

听风吹 2024-10-04 17:32:14

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

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

Have a look at something like this (full example)

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

回复收藏 0 原文

小猫一只 2024-10-04 17:32:14

试试这个 -

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 的一个旧行。否则子查询将返回多个值

try this -

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

This is assuming you only have one older row for nr old in the table. else the subquery will return more than one value

回复收藏 0 原文

~没有更多了~