使用 CTE 删除似乎不起作用

Question

我有以下查询，它使用多个CTE：

;with Test as (select plan_id, max("id") maxid
from Scheme
group by plan_id),
Rec as (select rh.* from Scheme rh, Test
where rh.plan_id = Test.plan_id
and rh.id = Test.maxid
and rh.event_id = 20)
delete from Scheme rh USING Rec
where rh.id = "Rec".id;

我收到以下错误：

[Err] ERROR:  missing FROM-clause entry for table "Rec"
LINE 9: where rh.id = "Rec".id;

如果我只使用一个 CTE 重写查询，那么使用"CTE"语法从"表"中删除是有效的。我在上面的查询中缺少什么？

Answer 1

错误的实际原因是无端大写和不正确的引用：

...
delete from Scheme rh USING Rec
where rh.id = "Rec".id;

在 Postgres 中，不带引号的标识符被强制转换为小写，并且实际上不区分大小写。如果双引号，则需要匹配大小写。 "Rec"与Rec不同。详：

• PostgreSQL 列名是否区分大小写？

您的对账单已清理：

WITH test AS (
   SELECT plan_id, max(id) AS id
   FROM   scheme
   GROUP  BY plan_id
   )
,  rec AS (
   SELECT s.*
   FROM   scheme s
   JOIN   test   t USING (plan_id, id)
   WHERE  s.event_id = 20
   )
DELETE FROM scheme s
USING  rec
WHERE  s.id = rec.id;

但这效率相当低下。 有更好的方法来标识每plan_id id最大的行。

使用DISTINCT ON和子查询而不是 CTE 更短、更快：

DELETE FROM scheme s
USING (
   SELECT DISTINCT ON (plan_id)
          plan_id, id, event_id 
   FROM   scheme
   ORDER  BY plan_id, id DESC NULLS LAST
   ) r
WHERE  r.event_id = 20
AND    s.id = r.id;

详情DISTINCT ON ：

• 选择每个分组依据组中的第一行？

Answer 2

您可以通过两种方式执行此操作。

使用IN运算符删除记录。

WITH Test
     AS (SELECT plan_id,
                Max("id") maxid
         FROM   Scheme
         GROUP  BY plan_id),
     Rec
     AS (SELECT rh.*
         FROM   Scheme rh,
                Test
         WHERE  rh.plan_id = Test.plan_id
                AND rh.id = Test.maxid
                AND rh.event_id = 20)
DELETE FROM Scheme
WHERE  id IN(SELECT a.id
             FROM   rec a);

或者通过使用 join，您也可以执行此操作Using但构造将在两个表之间具有公共列，而不是表名。

WITH Test
     AS (SELECT plan_id,
                Max("id") maxid
         FROM   Scheme
         GROUP  BY plan_id),
     Rec
     AS (SELECT rh.*
         FROM   Scheme rh,
                Test
         WHERE  rh.plan_id = Test.plan_id
                AND rh.id = Test.maxid
                AND rh.event_id = 20)
DELETE FROM Scheme join rec using(id);