MySQL中多次自连接同一表时如何避免产生重复行

重复行的核心原因是同一个invoice_id在某张自连接的子表中存在多条匹配记录，比如某发票对应多个product_id='7078'的行，或多个total_price='0'的行，左连接会生成笛卡尔积，导致结果出现重复。

以下两种方法都能实现按invoice_id输出唯一行的需求：

方法一：聚合函数+分组 #

通过MAX()/MIN()聚合属性值，再按invoice_id分组，直接确保每个发票只返回一行：

SELECT 
    t.invoice_id,  
    MAX(a1.product_price) AS attr_val1,
    MAX(a2.product_price) AS attr_val2,
    MAX(a3.product_price) AS attr_val3,
    MAX(a4.total_price) AS attr_val4
FROM `table` t
    LEFT JOIN `table` a1 ON t.invoice_id=a1.invoice_id AND a1.product_id='7078'
    LEFT JOIN `table` a2 ON t.invoice_id=a2.invoice_id AND a2.product_id='7071'
    LEFT JOIN `table` a3 ON t.invoice_id=a3.invoice_id AND a3.product_id='7085'
    LEFT JOIN `table` a4 ON t.invoice_id=a4.invoice_id AND a4.total_price='0'
GROUP BY t.invoice_id

注：MAX()和MIN()任选其一即可，只要对应发票下该属性值唯一。

方法二：子查询去重后再连接 #

先对每个自连接的子表用DISTINCT去重，确保每个invoice_id只返回一条记录，再执行左连接避免笛卡尔积：

SELECT 
    t.invoice_id,  
    a1.product_price AS attr_val1,
    a2.product_price AS attr_val2,
    a3.product_price AS attr_val3,
    a4.total_price AS attr_val4
FROM `table` t
    LEFT JOIN (SELECT DISTINCT invoice_id, product_price FROM `table` WHERE product_id='7078') a1 
        ON t.invoice_id=a1.invoice_id
    LEFT JOIN (SELECT DISTINCT invoice_id, product_price FROM `table` WHERE product_id='7071') a2 
        ON t.invoice_id=a2.invoice_id
    LEFT JOIN (SELECT DISTINCT invoice_id, product_price FROM `table` WHERE product_id='7085') a3 
        ON t.invoice_id=a3.invoice_id
    LEFT JOIN (SELECT DISTINCT invoice_id, total_price FROM `table` WHERE total_price='0') a4 
        ON t.invoice_id=a4.invoice_id

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