MySQL UNION查询同customer_id返回两行，如何修正得到合并结果

错误原因 #

• 语法逻辑使用错误：UNION的作用是垂直拼接两个结果集的行数据，不会按相同主键合并列数据。你编写的查询会将客户基础信息行、客户统计数据行作为完全独立的两行返回，自然无法合并为同一行展示完整信息。
• 原统计子查询中关联customer表属于冗余操作，不需要在统计逻辑中重复查询客户基础信息。

修正后的查询语句 #

SELECT 
  c.customer_id,
  c.name AS cname,
  CASE
    WHEN c.address <> '' THEN CONCAT(c.address, ', ', c.city)
    ELSE c.city
  END AS address,
  s.total_sale,
  s.total_paid,
  s.total_due
FROM customer c
-- 左连接关联统计结果，无消费记录的客户也会保留基础信息
LEFT JOIN (
  SELECT 
    customer_id,
    sum(amount_due_tot) AS total_sale,
    sum(amount_paid_tot) AS total_paid,
    sum(amount_due_balance_tot) AS total_due
  FROM (
    SELECT 
      bad.basket_id AS baskets,
      bad.customer_id AS customer_id,
      bad.amount_due AS amount_due_tot,
      sum(COALESCE(p.amount_paid, 0)) AS amount_paid_tot,
      bad.amount_due - SUM(COALESCE(p.amount_paid, 0)) AS amount_due_balance_tot,
      sale_status_id
    FROM basket_amount_due bad
    LEFT JOIN basket b USING (basket_id)
    LEFT JOIN basket_payment p USING (basket_id)
    WHERE b.sale_status_id = 4
    GROUP BY basket_id, sale_status_id
  ) d
  GROUP BY customer_id
) s ON c.customer_id = s.customer_id
ORDER BY c.customer_id;

逻辑说明 #

• 用LEFT JOIN替代UNION，主表保留所有客户的基础信息，关联子查询得到的消费统计数据，会自动按customer_id匹配到对应客户的同一行中。
• 没有消费记录的客户，统计字段会默认返回NULL，完全符合预期结果的要求。

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