SQLite中GROUP_CONCAT在查询中的异常行为问题

这个问题我太熟了——你遇到的是LEFT JOIN产生笛卡尔积导致的重复拼接问题！

原因很简单：当你直接在两个都包含多条同一ID记录的表上做LEFT JOIN时，会生成笛卡尔积。比如items_functions里某个ID有2条记录，items_functions_2里同一个ID有3条记录，JOIN后就会出现2×3=6条该ID的记录，GROUP_CONCAT会把这些重复组合后的内容全部拼进去，最终就出现了重复的结果。

正确的解决方案：先分组拼接，再关联 #

我们可以先分别对两个表单独做分组拼接，确保每个ID在子查询里只有一条记录，之后再做JOIN，这样就不会产生笛卡尔积了：

SELECT
    COALESCE(f.id, r.id) AS id,
    f.key_value_pair_1,
    r.key_value_pair_2
FROM
    -- 先处理第一个表的分组拼接
    (SELECT 
         id, 
         '{{' || group_concat(key||','||ifnull(value,'NULL'), '},{')||'}}' AS key_value_pair_1
     FROM items_functions
     GROUP BY id) AS f
LEFT JOIN
    -- 再处理第二个表的分组拼接
    (SELECT 
         id, 
         '{{' || group_concat(key||','||ifnull(value,'NULL'), '},{')||'}}' AS key_value_pair_2
     FROM items_functions_2
     GROUP BY id) AS r 
ON f.id = r.id

额外说明 #

如果你的业务需要包含items_functions_2中存在但items_functions中没有的ID，可以把LEFT JOIN换成FULL JOIN（注意：MySQL 8.0及以上版本支持FULL JOIN，低版本可以用UNION ALL结合分组来实现类似效果）。

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