如何在Postgres中删除CTE(Common Table Expression)中的记录

问题描述

我在Postgres中使用CTE编写了以下查询。现在我无法删除其中的记录。

WITH cte AS (
    SELECT 
   
        firstname, 
        lastname, 
        country, 
        ROW_NUMBER() OVER (
            PARTITION BY 
                firstname, 
                lastname, 
                country
        ) row_num
     FROM 
        employee
) 
delete  from cte
where row_num >1

当我运行此查询时，显示错误：

这是我的表‘Employee’的示例

id  firstname  lastname  country
1   "Raj"      "Gupta"   "India"
2   "Raj"      "Gupta"   "India"
3   "Mohan"    "Kumar"   "USA"
4   "James"    "Barry"   "UK"
5   "James"    "Barry"   "UK"
6   "James"    "Barry"   "UK"

推荐答案

无法从CTE中删除。

您可以这样做：

demo:db<>fiddle

DELETE FROM employee
WHERE id IN (
    SELECT
        id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY firstname, lastname, country) row_num
        FROM 
            employee
    ) s
    WHERE row_num > 1
)

如果您仍然要使用CTE，则可以将子查询移到其中：

demo:db<>fiddle

WITH cte AS (
    SELECT
        id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY firstname, lastname, country) row_num
        FROM 
            employee
    ) s
    WHERE row_num > 1
)
DELETE FROM employee
WHERE id IN (SELECT * FROM cte)

这篇关于如何在Postgres中删除CTE(Common Table Expression)中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！