SQLite使用group_concat出现重复值且无法用DISTINCT的查询问题求助

问题原因 #

多表左连接时会产生笛卡尔积，单条File记录匹配N条Video、M条Audio、K条Subtitle记录时，JOIN后的总数据行是NMK，直接在顶层做GROUP_CONCAT会把同个字段的值重复拼接多次，导致出现多余重复值。

解决方案 #

先分别对Video、Audio、Subtitle三个子表按FileID做单独聚合，再将聚合后的结果和File主表关联，避免产生笛卡尔积。

修改后的查询语句 #

SELECT  
    m.ID AS num,
    m.Name AS MOVIE,
    IFNULL(v.v_CODEC, '') AS v_CODEC,
    IFNULL(a.a_CODEC, '') AS a_CODEC,
    IFNULL(s.SUBs, '') AS SUBs
FROM File AS m
LEFT JOIN (
    SELECT FileID, GROUP_CONCAT(Codec SEPARATOR '-') AS v_CODEC
    FROM Video
    GROUP BY FileID
) AS v ON m.ID = v.FileID
LEFT JOIN (
    SELECT FileID, GROUP_CONCAT(Codec SEPARATOR '-') AS a_CODEC
    FROM Audio
    GROUP BY FileID
) AS a ON m.ID = a.FileID
LEFT JOIN (
    SELECT FileID, GROUP_CONCAT(Language SEPARATOR '-') AS SUBs
    FROM Subtitle
    GROUP BY FileID
) AS s ON m.ID = s.FileID
ORDER BY m.ID

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