MySQL三表关联按国籍分组拼接中心名并统计用户数的实现问题

正确SQL实现 #

SELECT 
  u.nationality,
  GROUP_CONCAT(DISTINCT c.center_name SEPARATOR ',') AS related_center_names,
  COUNT(DISTINCT u.user_id) AS nationality_user_total
FROM users u
LEFT JOIN centers_users cu ON u.user_id = cu.student_id
LEFT JOIN centers c ON cu.center_id = c.id
GROUP BY u.nationality;

关键逻辑说明 #

• 冗余问题的根源是多表关联后，1个对应多个中心的用户会生成多条记录，直接按国籍分组会导致用户计数重复、中心名拼接重复。
• COUNT(DISTINCT u.user_id) 会对用户ID去重统计，保证每个用户只被计数1次，得到准确的国籍用户总数。
• GROUP_CONCAT(DISTINCT c.center_name) 会对中心名称去重后再拼接，避免同一个中心名多次出现。如果需要自定义拼接分隔符，修改SEPARATOR后面的参数即可，默认分隔符为逗号。
• 使用LEFT JOIN会保留没有关联任何中心的用户，如果你不需要统计这类用户，可以替换为INNER JOIN。

注意事项 #

MySQL默认GROUP_CONCAT的最大拼接长度为1024字节，如果中心名称较多可能会被截断，可以在执行查询前先运行如下语句调大当前会话的拼接长度限制：

SET SESSION group_concat_max_len = 102400;

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