如何在PostgreSQL上将选择row_number查询转换为删除查询？

cetgtptt 于 2023-02-15 发布在 PostgreSQL

关注(0)|答案(1)|浏览(130)

我有一个这样的查询，它在postgresql服务器上运行良好;

SELECT *
FROM
  (SELECT s_o_i,
          row_number() OVER (PARTITION BY g_i,
                                          c
                             ORDER BY o_s DESC) AS rank
   FROM b_o) b_o
WHERE rank > 1
  AND m_t < CURRENT_DATE - interval '1 months'

当我把这个转换成这个查询的时候;

DELETE
FROM
  (SELECT s_o_i,
          row_number() OVER (PARTITION BY g_i,
                                          c
                             ORDER BY o_s DESC) AS rank
   FROM b_o) b_o
WHERE rank > 1
  AND m_t < CURRENT_DATE - interval '1 months'

它会抛出这样的错误

ERROR:  syntax error at or near "("
LINE 3:   (SELECT s_o_i,

为什么它在删除查询时抛出错误？

DELETE
FROM
  (SELECT s_o_i,
          row_number() OVER (PARTITION BY g_i,
                                          c
                             ORDER BY o_s DESC) AS rank
   FROM b_o) b_o
WHERE rank > 1
  AND m_t < CURRENT_DATE - interval '1 months'

我希望它运行并删除条款上的记录。

postgresql

来源：https://stackoverflow.com/questions/75395880/how-to-convert-select-row-number-query-to-delete-query-on-postgresql

1条答案

按热度按时间

nnsrf1az1#

假设您的目标是从每组(g_i, c)中删除一个多月前的所有记录，除了最新/最高的o_s：online demo

DELETE FROM b_o WHERE ctid in 
(   SELECT ctid
    FROM 
    (   SELECT  ctid,
                row_number() OVER w AS rank                             
        FROM b_o 
        WHERE m_t < CURRENT_DATE - interval '1 months'
        WINDOW w AS (PARTITION BY g_i, c ORDER BY o_s DESC)
    ) as alias
    WHERE rank > 1
);

1.您试图从subselect中删除记录，而不是从表b_o中删除记录，这在语法上是不正确的，也没有多大意义，因为子查询表只在外部查询期间存在。
1.您在子查询外引用了m_t列，但没有先在那里选择它，因此无法识别它。
1.如果s_o_i列唯一标识表中的记录，则可以使用它来代替ctid。
1.在这种情况下，common table expressions不是必需的，但是您可以使用一个来使事物变得更扁平：

WITH cte AS 
(   SELECT  ctid,
            row_number() OVER w AS rank                             
    FROM b_o 
    WHERE m_t < CURRENT_DATE - interval '1 months'
    WINDOW w AS (PARTITION BY g_i, c ORDER BY o_s DESC)
)
DELETE FROM b_o WHERE ctid in 
(   SELECT ctid
    FROM cte
    WHERE rank > 1
);

赞(0）回复(0）举报 2023-02-15

我来回答

如何在PostgreSQL上将选择row_number查询转换为删除查询？

1条答案

相关问题

热门标签

最新问答