MySQL多表连接结合聚合函数时如何去除重复值

问题场景 #

连接volunteers、volunteer_category、categories三张表时，用JSON_ARRAYAGG能得到无重复的分类数组；但加入volunteer_currency和currencies表后，分类和货币数组都出现大量重复值——原因是分类和货币属于多对多关联，交叉连接后每条分类会与每条货币组合，导致聚合时重复统计。

有重复问题的原查询：

SELECT DISTINCT v.id, v.name, v.type, v.logo, v.description, 
       JSON_ARRAYAGG(c.category) AS categories, 
       JSON_ARRAYAGG(cur.code) AS currencies 
FROM volunteers v 
INNER JOIN volunteer_category vc ON v.id = vc.volunteer_id 
INNER JOIN categories c ON vc.category_id = c.id
INNER JOIN volunteer_currency vcur ON v.id = vcur.volunteer_id
INNER JOIN currencies cur ON vcur.currency_id = cur.id
GROUP BY v.id

解决方法 #

方法1：在JSON_ARRAYAGG中直接去重 #

语法简单，适合数据量较小的场景，直接在聚合函数内加入DISTINCT关键字：

SELECT v.id, v.name, v.type, v.logo, v.description, 
       JSON_ARRAYAGG(DISTINCT c.category) AS categories, 
       JSON_ARRAYAGG(DISTINCT cur.code) AS currencies 
FROM volunteers v 
INNER JOIN volunteer_category vc ON v.id = vc.volunteer_id 
INNER JOIN categories c ON vc.category_id = c.id
INNER JOIN volunteer_currency vcur ON v.id = vcur.volunteer_id
INNER JOIN currencies cur ON vcur.currency_id = cur.id
GROUP BY v.id, v.name, v.type, v.logo, v.description

注意：MySQL 5.7及以上支持JSON_ARRAYAGG，且严格模式下GROUP BY需包含所有非聚合字段，因此要把v.name等字段都加入分组条件。

方法2：子查询预聚合（推荐，适配后续加更多表） #

先分别对分类、货币数据做预聚合，再与主表连接，从根源避免交叉连接产生的冗余数据，性能更优：

SELECT v.id, v.name, v.type, v.logo, v.description,
       cat.categories,
       cur.currencies
FROM volunteers v
-- 预聚合志愿者的分类数据
INNER JOIN (
    SELECT vc.volunteer_id, JSON_ARRAYAGG(c.category) AS categories
    FROM volunteer_category vc
    INNER JOIN categories c ON vc.category_id = c.id
    GROUP BY vc.volunteer_id
) cat ON v.id = cat.volunteer_id
-- 预聚合志愿者的货币数据
INNER JOIN (
    SELECT vcur.volunteer_id, JSON_ARRAYAGG(cur.code) AS currencies
    FROM volunteer_currency vcur
    INNER JOIN currencies cur ON vcur.currency_id = cur.id
    GROUP BY vcur.volunteer_id
) cur ON v.id = cur.volunteer_id

这种方式的优势是：后续新增其他多对多关联表时，只需新增对应的预聚合子查询，不会让主查询的关联数据量急剧膨胀，始终保持高效。

关于你尝试的JSON数组关联问题 #

你之前先聚合货币ID数组再关联currencies表的思路没必要——预聚合时直接关联currencies拿到code，比聚合ID后再解析数组关联更简单高效，因此推荐使用方法2的预聚合方案。

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