主页 / dba / 问题 / 247169
Accepted
Chessbrain
Asked: 2019-09-07 04:37:03 +0800 CST

有一个列在表中，但不能从这部分查询中引用

  • 772

我一直在学习 Postgres（来自 SQL Server），这个错误真的让我很困惑。

这是带有一些示例数据的代码：

create table T (
ID serial primary key,
A varchar(1),
B varchar(1),
C varchar(1)
)

↑ 测试表。

insert into T (A, B, C)
values('A', 'B', 'C'), ('A', 'B', 'C')

↑ 插入重复项

delete from T
where ID in (
    select t.ID
    from (  select ID, row_number() over (partition by A,B,C order by A,B,C) as rn
            from T) as t
    where t.rn < (select max(t.rn) from t)
    )

↑ 删除重复项，保留最后一个条目。

问题出在(select max(t.rn) from t)我假设这是一个新手错误，与在使用别名引用列时不知道 postgres 语法有关？

postgresql subquery

1 个回答

  1. Best Answer

    首先，partition by A,B,C order by A,B,C没有意义。由于您打算保留“最后”行（意思是最大的那一行ID），您可能的意思是：

    partition by A,B,C order by ID

    尽管如此，语法仍然无效。此子查询表达式包含对外部查询列的引用：(select max(t.rn) from t)。子查询的范围不包括外部查询中的列，因此rn在那里不可见。只有表格的列t是。

    您可以使用 CTE 来允许引用并使语法有效：

    WITH cte AS (SELECT id, row_number() OVER (PARTITION by a,b,c ORDER BY id) AS rn FROM t)
DELETE FROM t
WHERE  id IN (
    SELECT id
    FROM   cte
    WHERE  rn < (SELECT max(rn) FROM cte)
    )

    不过，这个查询是危险的废话。不要使用这个！
    比较最大的行数是逻辑上的废话，因为每组对等点可能有不同数量的欺骗。会删除比它应该删除的更多。

    更简单，更正确：

    DELETE FROM t
WHERE  id IN (
    SELECT t1.id
    FROM  (SELECT id, row_number() OVER (PARTITION by a,b,c ORDER BY id DESC) AS rn FROM t) t1
    WHERE  t1.rn > 1  --  all but the latest
    );

    反过来，它可以更便宜地获得（假设所有列NOT NULL！）：

    DELETE FROM t
WHERE  EXISTS (
   SELECT FROM t AS t1
   WHERE t1.a = t.a
   AND   t1.b = t.b
   AND   t1.c = t.c
   AND   t1.id > t.id
   );

    “...存在具有更大 ID 的骗子”。

    有关的：

    • 3

相关问题