PostgreSQL中间关系无法在WHERE子句引用原因及无主键表去重问题

1. 为什么PostgreSQL查询中创建的中间关系无法在WHERE子句中引用？ #

这其实是由SQL语句的执行顺序决定的，PostgreSQL（以及几乎所有关系型数据库）都会按照固定的逻辑顺序执行查询的各个子句，大致流程是：

1. 先执行FROM和JOIN子句，确定要查询的数据源
2. 接着执行WHERE子句，过滤掉不符合条件的行
3. 然后进行GROUP BY分组，再用HAVING过滤分组结果
4. 之后才会执行SELECT子句，计算列值、生成别名或者中间关系
5. 最后执行ORDER BY、LIMIT/OFFSET这类排序和分页操作

你看，WHERE的执行时机远早于SELECT，当数据库处理WHERE条件时，SELECT里定义的中间关系（比如列别名、计算字段）还没被计算出来，自然就没法引用了。

举个典型的错误例子：

SELECT id + 1 AS new_id
FROM customer_temp
WHERE new_id > 5; -- 这里会报错，因为new_id在WHERE阶段还不存在

如果要实现类似逻辑，你可以用这些替代方案：

• 把计算逻辑直接写到WHERE里：WHERE id + 1 > 5
• 用子查询或者CTE（公共表表达式）先计算出中间结果，再在外层过滤：

  WITH temp AS (
    SELECT id + 1 AS new_id FROM customer_temp
  )
  SELECT * FROM temp WHERE new_id > 5;
  

2. 如何删除无主键表中的重复数据？ #

针对你提供的customer_temp表数据，我给你两种实用的方法，核心思路都是先标记重复行，再删除多余的副本：

方法一：利用CTE和窗口函数（推荐） #

PostgreSQL的窗口函数可以帮我们给重复行编号，然后只保留编号为1的行，删除其他的。这里我们用ctid（PostgreSQL中每行的唯一标识符，无主键时可以用它区分不同行）来定位要删除的记录：

WITH ranked_customers AS (
  SELECT 
    ctid, -- 每行的唯一标识
    ROW_NUMBER() OVER (
      PARTITION BY id, firstname, country, phonenumber -- 按这些字段分组，判断重复
      ORDER BY (SELECT NULL) -- 不指定排序规则，随机保留一条
    ) AS rn
  FROM customer_temp
)
DELETE FROM customer_temp c
USING ranked_customers rc
WHERE c.ctid = rc.ctid AND rc.rn > 1; -- 删除编号大于1的重复行

方法二：用子查询直接删除 #

如果觉得CTE有点复杂，也可以用子查询实现：

DELETE FROM customer_temp
WHERE ctid NOT IN (
  SELECT MIN(ctid) -- 保留每组重复行中ctid最小的那一条
  FROM customer_temp
  GROUP BY id, firstname, country, phonenumber -- 按重复字段分组
);

注意：PARTITION BY或者GROUP BY后面的字段要根据你的实际重复判断标准调整，如果重复的定义只是id相同，那只写PARTITION BY id就可以了。

内容的提问来源于stack exchange，提问作者Mangu Singh Rajpurohit