MySQL多对多关联查询中GROUP_CONCAT服务字段去重问题求助

核心解决办法是在GROUP_CONCAT函数中添加DISTINCT关键字，直接对拼接的字段去重。

修改后的SQL语句 #

SELECT 
    GROUP_CONCAT(containers.container_id ORDER BY containers.container_id DESC) AS container_id, 
    GROUP_CONCAT(containers.container_serial ORDER BY containers.container_id DESC) AS container_serial, 
    GROUP_CONCAT(containers.container_index ORDER BY containers.container_id DESC) AS container_index, 
    invoices.*, 
    statuses.status, 
    clients.client, 
    GROUP_CONCAT(DISTINCT services.service_id ORDER BY services.service_id DESC) AS service_id, 
    GROUP_CONCAT(DISTINCT services.service ORDER BY services.service_id DESC) AS service 
FROM containers_invoices 
LEFT JOIN containers 
    ON containers_invoices.container_id = containers.container_id 
LEFT JOIN invoices 
    ON containers_invoices.invoice_id = invoices.invoice_id 
LEFT JOIN statuses 
    ON invoices.status_id = statuses.status_id 
LEFT JOIN clients 
    ON invoices.client_id = clients.client_id 
LEFT JOIN invoices_services 
    ON invoices.invoice_id = invoices_services.invoice_id 
LEFT JOIN services 
    ON invoices_services.service_id = services.service_id 
GROUP BY containers_invoices.invoice_id 
ORDER BY invoice_id DESC 

关键修改说明 #

• 针对service_id和service的GROUP_CONCAT，在字段前添加DISTINCT关键字，过滤重复值
• 调整排序依据为services.service_id（也可根据需求改为services.service），避免因容器排序导致的不必要顺序问题

效果说明 #

修改后查询结果中的service字段会自动去重：

• 原结果中Service,Service会变为Service
• 原结果中Line,Line,Line,Line会变为Line

问题根源 #

由于invoices与services是多对多关联，同时又关联了containers表，多表联查时会生成笛卡尔积，导致每个service记录会与每个container记录重复匹配，最终在GROUP_CONCAT时出现重复值。添加DISTINCT可直接在聚合阶段过滤掉这些重复项。

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