时间戳

首页 > 解决方案 > SQL 更新表中的记录，涉及同一表中的另一条记录

sql - SQL 更新表中的记录，涉及同一表中的另一条记录

问题描述

我有一个包含超过 16,000,000 条记录的表。每条记录都有一个主键（由五个字段“tsid、plisid、plifc、plisc、dt”组成）和两个计数器字段（“icount、aicount”）。

表中的一些记录之间存在关系。

为了简化问题，假设我们只有这两条记录

tsid, plisid, plifc, plisc, dt, icount, aicount
10    1       0      0      0   2       2
11    1       0      0      0   7       0

要求：

我想将第二条记录中的“aicount”字段更新为9（即第二条记录中的“icount”+第一条记录中的“aicount”）。第一条记录和第二条记录的关系是它们在(plisid, plifc, plisc, dt)中的值相同，第二条记录的tsid值==第一条记录的tsid+1

更新后想要的结果是：

tsid, plisid, plifc, plisc, dt, icount, aicount
10    1       0      0      0   2       2
11    1       0      0      0   7       9

我在 PostgreSQL 中尝试了这个 SQL 语句，但出现语法错误“错误：在“SELECT”位置或附近出现语法错误：59”

UPDATE table1 SET
table1.aicount = table1.icount + SELECT COALESCE( (SELECT CASE
    WHEN table1temp.aicount IS NULL
    THEN 0
    ELSE table1temp.aicount
    END
  FROM table1 table1temp
  WHERE table1temp.tsid = table1.tsid - 1
        AND table1temp.plisid = table1.plisid
        AND table1temp.plifc = table1.plifc
        AND table1temp.plisc = table1.plisc
        AND table1temp.dt = table1.dt), 0)
  WHERE table1.tsid = 10;

上面的陈述有什么问题？有什么想法或建议吗？

标签： sqlpostgresql

解决方案

由于您无法使用select子查询添加更新列而导致的错误。

你似乎想得到这个行icount号与最后记录的数字相加的aicount数字

我会使用LAG函数在子查询中获取最后记录aicount的数字，然后更新数字。

函数中有三个参数LAG。

  1. 首先是您的列，您想要获取最后一列的值。
  2. 此列值的偏移量默认为 1
  3. 默认值。默认为null

滞后（值任何[，偏移整数[，默认任何]]）

返回在分区内当前行之后的偏移行处计算的值；如果没有这样的行，则返回默认值。偏移量和默认值都相对于当前行进行评估。如果省略，偏移量默认为 1，默认为 null

创建表T（tsid int，plisid int，plifc int，plisc int，dt int，icount int，aicount int）；

INSERT INTO T VALUES (10,1,0,0,0,2,2);
INSERT INTO T VALUES (11,1,0,0,0,7,0);


UPDATE T 
SET aicount = t1.totle
FROM 
(
  SELECT *,(LAG(aicount,1,0) over(order by tsid) + icount) totle
  FROM T
) t1 
WHERE 
T.tsid = t1.tsid 
AND T.plisid = t1.plisid
AND T.plifc = t1.plifc
AND T.plisc = t1.plisc
AND T.dt = t1.dt

查询 1：

SELECT * FROM T

结果：

| tsid | plisid | plifc | plisc | dt | icount | aicount |
|------|--------|-------|-------|----|--------|---------|
|   10 |      1 |     0 |     0 |  0 |      2 |       2 |
|   11 |      1 |     0 |     0 |  0 |      7 |       9 |

推荐阅读