多表JOIN查询存在重复行，如何合并为每个客户仅返回单条记录

问题根源 #

你遇到的重复行是笛卡尔积导致的：同一个客户的M条购买商品记录和N条退货商品记录直接关联时，会生成M*N条数据，也就是你看到的2个购买+2个退货返回4条重复记录的情况。

实现方案 #

不需要复杂逻辑，只要先在子查询中分别按客户维度聚合所有购买商品、退货商品，再和客户表、省份表关联即可，主流数据库都有内置的字符串拼接聚合函数，用法非常简单：

示例代码 #

SELECT 
  CUSTOMER.CUSTFNAME || ' ' || CUSTOMER.CUSTLNAME AS "CUSTOMER",
  COALESCE(order_agg.items_purchased, '') AS "ITEMS PURCHASED",
  COALESCE(return_agg.returns, '') AS "RETURNS",
  STATES.STATENAME
FROM CUSTOMER 
INNER JOIN STATES ON CUSTOMER.STATEID = STATES.STATEID
-- 关联预聚合的购买商品数据
LEFT JOIN (
  SELECT 
    ORDER.CUSTID,
    -- 不同数据库替换对应聚合函数即可：
    -- MySQL：GROUP_CONCAT(ORDERITEM.ITEMDESC SEPARATOR ', ')
    -- PostgreSQL / SQL Server：STRING_AGG(ORDERITEM.ITEMDESC, ', ')
    -- Oracle：LISTAGG(ORDERITEM.ITEMDESC, ', ') WITHIN GROUP (ORDER BY ORDERITEM.ITEMDESC)
    STRING_AGG(ORDERITEM.ITEMDESC, ', ') AS items_purchased
  FROM ORDER 
  INNER JOIN ORDERITEM ON ORDER.OITEMID = ORDERITEM.OITEMID
  GROUP BY ORDER.CUSTID
) order_agg ON CUSTOMER.CUSTID = order_agg.CUSTID
-- 关联预聚合的退货商品数据
LEFT JOIN (
  SELECT 
    RETURN.CUSTID,
    -- 同上替换对应数据库聚合函数
    STRING_AGG(RETURNITEM.ITEMDESCS, ', ') AS returns
  FROM RETURN 
  INNER JOIN RETURNITEM ON RETURN.RITEMID = RETURNITEM.RITEMID
  GROUP BY RETURN.CUSTID
) return_agg ON CUSTOMER.CUSTID = return_agg.CUSTID;

注意事项 #

• 子查询按客户ID分组后每个客户仅返回1行数据，关联后不会再产生笛卡尔积重复
• 使用LEFT JOIN是为了兼容没有购买记录/没有退货记录的客户也能正常返回，如果你只需要同时有购买和退货记录的客户，把LEFT JOIN改成INNER JOIN即可
• ORDER和RETURN都是SQL保留关键字，作为表名使用时建议加引号转义，避免执行报错

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