MYSQL 连接列而不分组

Question

我有一个包含国家/地区代码和语言 ID 的表

+------+------+-------------+
|  id  | iso  | language_id |
+------+------+-------------+
|  1   |  US  |      4      |
|  2   |  IE  |      1      |
|  3   |  DE  |      2      |
|  4   |  SG  |      1      |
|  5   |  FR  |      3      |
|  6   |  UK  |      1      |
|  7   |  AT  |      2      |
+------+------+-------------+

我需要的是一个 MySQL 语句，它将返回一个包含每个 ISO 的结果集和一个与语言 ID 匹配的 ID 连接字符串

所以在上面的例子中，我希望得到

+------+------+----------+
|  id  | iso  | id_group |
+------+------+----------+
|  1   |  US  |     4    |
|  2   |  IE  |  2,4,6   |
|  3   |  DE  |   3,7    |
|  4   |  SG  |  2,4,6   |
|  5   |  FR  |     5    |
|  6   |  UK  |  2,4,6   |
|  7   |  AT  |   3,7    |
+------+------+----------+

到目前为止，我的最佳尝试如下所示和 sqlfiddle 链接中所示，但分组不包括某些 ISO。我需要返回每一行

SELECT iso, language_id, GROUP_CONCAT(id) as id 
FROM countries 
GROUP BY language_id

http://sqlfiddle.com/#!9/907618/3

这可以通过 MySQL 完成吗？还是我需要运行许多语句才能获得结果？

谢谢

Answer 1

此查询将返回每种语言 ID 的所有 ID:

select language_id, GROUP_CONCAT(id ORDER BY id) as id_group
from countries 
group by language_id

那么您只需将此查询与国家/地区表连接起来即可:

select
  c.id,
  c.iso,
  g.id_group
from
  countries c inner join (
    select language_id, GROUP_CONCAT(id ORDER BY id) as id_group
    from countries 
    group by language_id
  ) g on c.language_id = g.language_id
order by
  c.id

如果没有子查询，您可以使用自连接:

select
  c.id,
  c.iso,
  group_concat(c1.id order by c1.id) as id_group
from
  countries c inner join countries c1
  on c.language_id = c1.language_id
group by
  c.id,
  c.iso