sql - 使用组值中的最后一行更新组中行的特定列

Question

考虑一下，我有以下表结构，我想用组值中的最后一行更新组中所有行的几列。输出应该是这样的：

所以我基本上根据entry_id列更新了组中的行，并更新了组中所有行的line_number和entered_by以匹配最后一行的值。

标签： sqlpostgresqlgroup-bysql-update

Answer 1

您可以使用以下内容获取结果

        WITH data
         AS (SELECT * FROM YOUR_TABLE),
         d1
         AS (SELECT entry_id,
                    Max(line_number)
                      over(
                        PARTITION BY entry_id
                        ORDER BY entry_id) line_number,
                    detailed_comment,
                    account_id
             FROM   data)
    SELECT d1.entry_id,
           d1.line_number,
           data.entered_by entered_by,
           d1.detailed_comment,
           d1.account_id
    FROM   d1,
           data
    WHERE  d1.entry_id = data.entry_id
           AND d1.line_number = data.line_number; 

首先使用备份表create test_backup as select * from test，然后运行以下 MERGE 语句

        MERGE INTO TEST s
    USING (
    SELECT distinct entry_id,
                        Max(line_number)
                          over(
                            PARTITION BY entry_id
                            ORDER BY entry_id) line_number,
                        detailed_comment,
                        account_id
                 FROM   TEST
    ) t
    ON (t.entry_id = s.entry_id )
    WHEN MATCHED THEN
    UPDATE SET s.entered_by= decode(s.line_number,t.line_number,s.entered_by,null),
    s.line_number=t.line_number

并第二次运行下面的 MERGE 语句

        MERGE INTO TEST s
    USING (
    SELECT distinct entry_id, Max(entered_by)
                          over(
                            PARTITION BY entry_id
                            ORDER BY entry_id) entered_by,
                        detailed_comment,
                        account_id
                 FROM   TEST
    ) t
    ON (t.entry_id = s.entry_id)
    WHEN MATCHED THEN
    UPDATE SET s.entered_by=t.entered_by;

和COMMIT;