PostgreSQL中如何在SQL查询其他位置引用SELECT子查询派生列

SQL执行顺序中WHERE子句先于SELECT子句运行，你在SELECT里定义的别名last_status在WHERE筛选阶段还未生成，数据库识别不到这个字段，所以抛出列不存在的错误。

以下三种方案都可在PostgreSQL中实现需求，可根据实际数据量和场景选择：

• 方案1：用CTE包装派生结果，外层做筛选
  先在CTE中完成预订记录和最新状态的关联计算，再在外层查询对last_status做条件过滤，逻辑最直观，改动成本最低：

  WITH booking_with_status AS (
      SELECT "bookings".*,
          (
              SELECT "status"
              FROM "booking_statuses"
              WHERE "bookings"."id" = "booking_statuses"."booking_id"
              ORDER BY "created_at" DESC
              LIMIT 1
          ) AS "last_status"
      FROM "bookings"
      WHERE "user_id" = $1
  )
  SELECT * FROM booking_with_status
  WHERE "last_status" IN ('PENDING', 'APPROVED');
  

• 方案2：用DISTINCT ON预计算最新状态再关联
  DISTINCT ON是PostgreSQL原生支持的语法，可以高效按分组取排序后的第一条记录，相比逐行执行的关联子查询，状态表数据量大时性能优势明显：

  SELECT b.*, bs."status" AS "last_status"
  FROM "bookings" b
  INNER JOIN (
      SELECT DISTINCT ON ("booking_id") "booking_id", "status"
      FROM "booking_statuses"
      ORDER BY "booking_id", "created_at" DESC
  ) bs ON b."id" = bs."booking_id"
  WHERE b."user_id" = $1
    AND bs."status" IN ('PENDING', 'APPROVED');
  

  这里用INNER JOIN即可，因为要筛选状态为指定值的记录，不存在需要保留无状态记录的场景。
• 方案3：用LATERAL横向关联取最新状态
  LATERAL是PostgreSQL支持的横向关联语法，可以在关联子查询中引用前序表的字段，逻辑和原写法的关联子查询一致，同时支持直接在WHERE中引用关联出的状态字段，还可以方便获取最新状态的其他属性（比如状态创建时间、操作人等）：

  SELECT b.*, bs."status" AS "last_status"
  FROM "bookings" b,
  LATERAL (
      SELECT "status"
      FROM "booking_statuses"
      WHERE "booking_id" = b."id"
      ORDER BY "created_at" DESC
      LIMIT 1
  ) bs
  WHERE b."user_id" = $1
    AND bs."status" IN ('PENDING', 'APPROVED');
  

不要为了省事在WHERE子句中重复写一遍和SELECT中完全相同的子查询做判断，这种写法虽然能正常执行，但同一个子查询会被重复执行两次，数据量大时性能损耗非常明显。

内容的提问来源于stack exchange，提问作者Obvious_Grapefruit