MySQL多表连接重复数据问题：如何合并单猫多条记录为单行？

嘿，你碰到的重复行问题其实是多表连接时的典型笛卡尔积现象——当同一只猫咪在CATDIET、CATMEDICAL、CATREQ这几张表中各有多条记录时，内连接会把每个表的记录进行全组合匹配，自然就生成了大量重复的行。

要把同一猫咪的所有属性合并成单行，核心思路是对每个猫咪的属性进行分组聚合，把多条属性值合并成一个用分隔符连接的字符串。下面分不同数据库给出具体的实现方案：

1. MySQL/MariaDB 方案 #

用GROUP_CONCAT()函数来聚合字符串，加上DISTINCT还能自动去掉同一属性里的重复值（如果有的话）：

SELECT 
    CATINFO.Chip_ID AS Cat,
    GROUP_CONCAT(DISTINCT CATDIET.Diet_Req SEPARATOR ', ') AS Diet,
    GROUP_CONCAT(DISTINCT CATMEDICAL.Med_his SEPARATOR ', ') AS Med,
    GROUP_CONCAT(DISTINCT CATREQ.Add_Req SEPARATOR ', ') AS Req
FROM CATINFO
INNER JOIN CATDIET ON CATINFO.Chip_ID = CATDIET.Chip_ID
INNER JOIN CATMEDICAL ON CATINFO.Chip_ID = CATMEDICAL.Chip_ID
INNER JOIN CATREQ ON CATINFO.Chip_ID = CATREQ.chip_ID
GROUP BY CATINFO.Chip_ID;

执行后，猫咪3的结果会变成清爽的单行：
'3', 'nut allergy', 'allergy emergency, hip surgery', 'left alone, tv on'

2. PostgreSQL 方案 #

PostgreSQL用STRING_AGG()函数，用法和MySQL的思路一致：

SELECT 
    CATINFO.Chip_ID AS Cat,
    STRING_AGG(DISTINCT CATDIET.Diet_Req, ', ') AS Diet,
    STRING_AGG(DISTINCT CATMEDICAL.Med_his, ', ') AS Med,
    STRING_AGG(DISTINCT CATREQ.Add_Req, ', ') AS Req
FROM CATINFO
INNER JOIN CATDIET ON CATINFO.Chip_ID = CATDIET.Chip_ID
INNER JOIN CATMEDICAL ON CATINFO.Chip_ID = CATMEDICAL.Chip_ID
INNER JOIN CATREQ ON CATINFO.Chip_ID = CATREQ.chip_ID
GROUP BY CATINFO.Chip_ID;

3. SQL Server 方案 #

SQL Server 2017及以上版本直接支持STRING_AGG()，写法和PostgreSQL差不多：

SELECT 
    CATINFO.Chip_ID AS Cat,
    STRING_AGG(DISTINCT CATDIET.Diet_Req, ', ') AS Diet,
    STRING_AGG(DISTINCT CATMEDICAL.Med_his, ', ') AS Med,
    STRING_AGG(DISTINCT CATREQ.Add_Req, ', ') AS Req
FROM CATINFO
INNER JOIN CATDIET ON CATINFO.Chip_ID = CATDIET.Chip_ID
INNER JOIN CATMEDICAL ON CATINFO.Chip_ID = CATMEDICAL.Chip_ID
INNER JOIN CATREQ ON CATINFO.Chip_ID = CATREQ.chip_ID
GROUP BY CATINFO.Chip_ID;

如果是2016及以下的旧版本，就得用STUFF结合FOR XML PATH的经典写法来实现聚合：

SELECT 
    c.Chip_ID AS Cat,
    STUFF((SELECT DISTINCT ', ' + d.Diet_Req FROM CATDIET d WHERE d.Chip_ID = c.Chip_ID FOR XML PATH('')), 1, 2, '') AS Diet,
    STUFF((SELECT DISTINCT ', ' + m.Med_his FROM CATMEDICAL m WHERE m.Chip_ID = c.Chip_ID FOR XML PATH('')), 1, 2, '') AS Med,
    STUFF((SELECT DISTINCT ', ' + r.Add_Req FROM CATREQ r WHERE r.Chip_ID = c.Chip_ID FOR XML PATH('')), 1, 2, '') AS Req
FROM CATINFO c
WHERE EXISTS(SELECT 1 FROM CATDIET d WHERE d.Chip_ID = c.Chip_ID)
  AND EXISTS(SELECT 1 FROM CATMEDICAL m WHERE m.Chip_ID = c.Chip_ID)
  AND EXISTS(SELECT 1 FROM CATREQ r WHERE r.Chip_ID = c.Chip_ID);

4. Oracle 方案 #

Oracle 11gR2及以上版本用LISTAGG()函数，还能指定排序：

SELECT 
    CATINFO.Chip_ID AS Cat,
    LISTAGG(DISTINCT CATDIET.Diet_Req, ', ') WITHIN GROUP (ORDER BY CATDIET.Diet_Req) AS Diet,
    LISTAGG(DISTINCT CATMEDICAL.Med_his, ', ') WITHIN GROUP (ORDER BY CATMEDICAL.Med_his) AS Med,
    LISTAGG(DISTINCT CATREQ.Add_Req, ', ') WITHIN GROUP (ORDER BY CATREQ.Add_Req) AS Req
FROM CATINFO
INNER JOIN CATDIET ON CATINFO.Chip_ID = CATDIET.Chip_ID
INNER JOIN CATMEDICAL ON CATINFO.Chip_ID = CATMEDICAL.Chip_ID
INNER JOIN CATREQ ON CATINFO.Chip_ID = CATREQ.chip_ID
GROUP BY CATINFO.Chip_ID;

额外小提示 #

• 如果有些猫咪在某张表中没有对应记录，又不想把它们过滤掉，可以把INNER JOIN换成LEFT JOIN，这样对应的属性列会显示NULL或者空字符串（取决于你用的数据库）。
• 如果能确定同一张表中同一只猫的属性没有重复值，可以去掉DISTINCT关键字，能稍微提升一点查询效率哦。

内容的提问来源于stack exchange，提问作者null.ts