MySQL关联3张表合并值出现重复，求正确SQL实现方案

问题背景 #

主表machine_list关联两张详情表applicable_rpm和applicable_product时，直接关联后使用GROUP_CONCAT会出现重复值。例如机器MN-1的rpm会显示为20,20，不符合预期结果。

表结构 #

• machine_list表

• applicable_rpm表

• applicable_product表

期望结果 #

错误原因 #

原SQL直接关联三张表会产生笛卡尔积：比如machine_id=2的记录，applicable_rpm有2条数据，applicable_product有2条数据，关联后生成2×2=4条中间记录，导致GROUP_CONCAT重复拼接相同值。

错误SQL：

SELECT t1.machine_number,
       GROUP_CONCAT(' ', t2.rpm) rpm,
       GROUP_CONCAT(' ', t3.productline) productline
FROM machine_list t1
INNER JOIN applicable_rpm t2 ON t1.id = t2.mc_recordID
INNER JOIN applicable_product t3 ON t1.id = t3.mc_recordID
GROUP BY t1.id;

解决方案 #

方案1：GROUP_CONCAT中用DISTINCT去重 #

在GROUP_CONCAT里加入DISTINCT过滤重复值，同时添加ORDER BY保证结果有序：

SELECT t1.machine_number,
       GROUP_CONCAT(DISTINCT t2.rpm ORDER BY t2.rpm SEPARATOR ', ') rpm,
       GROUP_CONCAT(DISTINCT t3.productline ORDER BY t3.productline SEPARATOR ', ') productline
FROM machine_list t1
INNER JOIN applicable_rpm t2 ON t1.id = t2.mc_recordID
INNER JOIN applicable_product t3 ON t1.id = t3.mc_recordID
GROUP BY t1.id, t1.machine_number;

注意：此方法适合不需要保留真实重复值的场景，若业务允许同一机器存在重复rpm或productline，会误删有效数据。

方案2：预聚合子查询（推荐） #

先分别对两张详情表按mc_recordID聚合，再与主表关联，从根源避免笛卡尔积，性能更优：

SELECT 
    t1.machine_number,
    COALESCE(t2.rpm_list, '') AS rpm,
    COALESCE(t3.product_list, '') AS productline
FROM machine_list t1
LEFT JOIN (
    SELECT mc_recordID, GROUP_CONCAT(rpm ORDER BY rpm SEPARATOR ', ') AS rpm_list
    FROM applicable_rpm
    GROUP BY mc_recordID
) t2 ON t1.id = t2.mc_recordID
LEFT JOIN (
    SELECT mc_recordID, GROUP_CONCAT(productline ORDER BY productline SEPARATOR ', ') AS product_list
    FROM applicable_product
    GROUP BY mc_recordID
) t3 ON t1.id = t3.mc_recordID;

说明：用LEFT JOIN兼容无对应rpm或productline的机器，若要求必须关联两张详情表，可替换为INNER JOIN；COALESCE用于处理空值，避免结果显示NULL。

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