PostgreSQL三表连接及Pizza Runner案例查询结果异常求助

问题背景 #

在完成Danny Ma的Pizza Runner SQL案例研究「定价与评分」章节时，Q1查询结果正确，但Q2、Q5结果与标准答案不符：

表结构与数据处理基础 #

CREATE TABLE customer_orders (  
  "order_id" INTEGER,  
  "customer_id" INTEGER,  
  "pizza_id" INTEGER,    
  "exclusions" VARCHAR(4),  
  "extras" VARCHAR(4),  
  "order_time" TIMESTAMP  
);  
-- 完整建表、插入数据及清理表的SQL代码见原始提问

Q2查询错误修正 #

错误原因 #

原查询通过LEFT JOIN extras连接配料表时，存在一对多关系（一个披萨可能对应多个额外配料），导致披萨记录被重复拆分，SUM(CASE WHEN pn.pizza_id = 1 THEN 12 ELSE 10 END)会重复计算同一披萨的价格，最终pizza_cost被放大。

修正后的SQL #

WITH pizza_total AS (
    -- 单独计算有效订单的披萨总费用，避免连接导致的重复计算
    SELECT SUM(CASE WHEN cco.pizza_id = 1 THEN 12 ELSE 10 END) AS pizza_cost
    FROM cleaned_customer_orders cco
    JOIN cleaned_runner_orders cro ON cro.order_id = cco.order_id
    WHERE cro.cancellation IS NULL
),
extras_total AS (
    -- 统计有效订单中额外配料的总费用
    SELECT COUNT(e.topping_id) * 1 AS extras_cost
    FROM cleaned_customer_orders cco
    JOIN cleaned_runner_orders cro ON cro.order_id = cco.order_id
    LEFT JOIN extras e ON e.record_id = cco.record_id
    WHERE cro.cancellation IS NULL
      AND e.topping_id IS NOT NULL
)
SELECT 
    pt.pizza_cost,
    COALESCE(et.extras_cost, 0) AS extras_cost,
    pt.pizza_cost + COALESCE(et.extras_cost, 0) AS total_cost
FROM pizza_total pt, extras_total et;

Q5查询错误修正 #

错误原因 #

原查询中，一个订单可能对应多个披萨，SUM(cro.distance*0.30)会将同一订单的配送距离重复计算多次（披萨数量=重复次数），导致delivery_cost被错误放大。

修正后的SQL #

WITH order_summary AS (
    -- 按订单分组，先计算每个订单的披萨总费用和单次配送成本
    SELECT 
        cro.order_id,
        cro.distance,
        SUM(CASE WHEN cco.pizza_id = 1 THEN 12 ELSE 10 END) AS order_pizza_cost
    FROM cleaned_runner_orders cro
    JOIN cleaned_customer_orders cco ON cro.order_id = cco.order_id
    WHERE cro.cancellation IS NULL
    GROUP BY cro.order_id, cro.distance
)
SELECT 
    SUM(order_pizza_cost) AS total_cost,
    SUM(distance * 0.30) AS delivery_cost,
    SUM(order_pizza_cost) - SUM(distance * 0.30) AS actual_cost
FROM order_summary;

PostgreSQL三表连接方法 #

核心连接类型 #

• INNER JOIN（内连接）：仅返回三个表中匹配连接条件的记录，是最常用的连接方式：

  SELECT *
  FROM table_a a
  JOIN table_b b ON a.id = b.a_id
  JOIN table_c c ON b.id = c.b_id;
  

• LEFT JOIN（左连接）：返回左表所有记录，右表无匹配则填充NULL，适合保留主表全量数据：

  SELECT *
  FROM table_a a
  LEFT JOIN table_b b ON a.id = b.a_id
  LEFT JOIN table_c c ON b.id = c.b_id;
  

• RIGHT JOIN/FULL JOIN：RIGHT JOIN返回右表全量记录，FULL JOIN返回左右表全量记录，实际业务中使用频率较低。

避坑要点 #

• 避免重复记录：当存在一对多关系时，必须通过GROUP BY分组聚合、DISTINCT去重，或拆分CTE单独计算各表统计值，再合并结果，防止笛卡尔积导致的重复计算。
• 连接顺序优化：PostgreSQL会自动优化连接顺序，但手动调整时建议先连接小表，再连接大表，提升查询效率。

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