postgresql 基于同一SELECT查询对多个表执行DELETE操作

67up9zun  于 2022-11-23  发布在  PostgreSQL
关注(0)|答案(1)|浏览(227)

我的表结构如下所述in this post

name | version | processed | processing | updated  | ref_time 
------+---------+-----------+------------+----------+----------
 abc  |       1 | t         | f          | 27794395 | 27794160
 def  |       1 | t         | f          | 27794395 | 27793440
 ghi  |       1 | t         | f          | 27794395 | 27793440
 jkl  |       1 | f         | f          | 27794395 | 27794160
 mno  |       1 | t         | f          | 27794395 | 27793440
 pqr  |       1 | f         | t          | 27794395 | 27794160

基于this answer,我正在导出一个ref_time值列表,我希望将其用作从status_table中删除“旧”条目的基础。
以下查询用于生成相关ref_time值的列表:

WITH main AS
(
    SELECT ref_time,
        ROUND(AVG(processed::int) * 100, 1) percent
    FROM status_table
    GROUP BY ref_time ORDER BY ref_time DESC, percent DESC
)
SELECT ref_time FROM main WHERE percent=100 OFFSET 2;

例如,这可能会传回:

ref_time 
----------
 27794880
 27794160

然后我可以用这个来DELETEstatus_table中的所有相关条目:

DELETE FROM status_table
WHERE ref_time IN 
(
    WITH main AS
    (
        SELECT ref_time,
            ROUND(AVG(processed::int) * 100, 1) percent
        FROM status_table
        GROUP BY ref_time ORDER BY ref_time DESC, percent DESC
    )
    SELECT ref_time FROM main WHERE percent=100 OFFSET 2
);

但是我有 * 另一个名为data_table的表 *,它 * 也 * 有一个ref_time列,我想在相同的基础上从该表中DELETE条目,即上面列表中有ref_time的任何行。
如何在不复制用于生成ref_time列表的查询的情况下实现这一点?

czq61nw1

czq61nw11#

可以使用公用表表达式:

with 
    ref as (
        select ref_time 
        from status_table 
        group by ref_time 
        having bool_and(processed)
        order by ref_time desc limit 2
    ),
    del_ref as (
        delete from status_table s
        using ref r
        where s.ref_time = r.ref_time
    )
delete from data_table d
using ref r
where d.ref_time = r.ref_time

第一个CTE ref返回您要从另外两个表中删除的时间戳列表。您似乎想要完全处理前2个时间戳(注意offset * 跳过 * 结果集中的那么多行,这与limit不同)。
第二个CTE从status_table删除,查询的最后一部分寻址data_table

相关问题