如何使用 GROUP BY 在 MySQL 中连接字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/149772/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use GROUP BY to concatenate strings in MySQL?
提问by Pawe? Hajdan
Basically the question is how to get from this:
基本上问题是如何从中获得：
foo_id foo_name 1 A 1 B 2 C
to this:
对此：
foo_id foo_name 1 A B 2 C
回答by Scott Noyes
SELECT id, GROUP_CONCAT(name SEPARATOR ' ') FROM table GROUP BY id;
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
From the link above, GROUP_CONCAT: This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.
从上面的链接中，GROUP_CONCAT：此函数返回一个字符串结果，其中包含来自组的连接的非 NULL 值。如果没有非 NULL 值，则返回 NULL。
回答by Graeme Perrow
SELECT id, GROUP_CONCAT( string SEPARATOR ' ') FROM table GROUP BY id
More details here.
更多细节在这里。
From the link above, GROUP_CONCAT: This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.
从上面的链接中，GROUP_CONCAT：此函数返回一个字符串结果，其中包含来自组的连接的非 NULL 值。如果没有非 NULL 值，则返回 NULL。
回答by Wayne
SELECT id, GROUP_CONCAT(CAST(name as CHAR)) FROM table GROUP BY id
Will give you a comma-delimited string
会给你一个逗号分隔的字符串
回答by Exundoz
SELECT id, GROUP_CONCAT(name SEPARATOR ' ') FROM table GROUP BY id;
:- In MySQL, you can get the concatenated values of expression combinations . To eliminate duplicate values, use the DISTINCTclause. To sort values in the result, use the ORDER BY clause. To sort in reverse order, add the DESC(descending) keyword to the name of the column you are sorting by in the ORDER BY clause. The default is ascending order; this may be specified explicitly using the ASC keyword. The default separator between values in a group is comma (“,”). To specify a separator explicitly, use SEPARATOR followed by the string literal value that should be inserted between group values. To eliminate the separator altogether, specify SEPARATOR ''.
:- 在 MySQL 中，您可以获得表达式组合的连接值。要消除重复值，请使用DISTINCT子句。要对结果中的值进行排序，请使用 ORDER BY 子句。要按相反顺序排序，请将DESC（降序）关键字添加到您在 ORDER BY 子句中作为排序依据的列的名称。默认为升序；这可以使用 ASC 关键字明确指定。组中值之间的默认分隔符是逗号 (“,”)。要显式指定分隔符，请使用 SEPARATOR 后跟应插入组值之间的字符串文字值。要完全消除分隔符，请指定SEPARATOR ''。
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
OR
或者
mysql> SELECT student_name,
-> GROUP_CONCAT(DISTINCT test_score
-> ORDER BY test_score DESC SEPARATOR ' ')
-> FROM student
-> GROUP BY student_name;
回答by Waqar Alamgir
The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024 characters, so we first do:
结果被截断为 group_concat_max_len 系统变量给定的最大长度，其默认值为 1024 个字符，因此我们首先执行：
SET group_concat_max_len=100000000;
and then, for example:
然后，例如：
SELECT pub_id,GROUP_CONCAT(cate_id SEPARATOR ' ') FROM book_mast GROUP BY pub_id
回答by Mauricio Alo
Great answers. I also had a problem with NULLS and managed to solve it by including a COALESCE inside of the GROUP_CONCAT. Example as follows:
很棒的答案。我也遇到了 NULLS 问题，并设法通过在 GROUP_CONCAT 中包含 COALESCE 来解决它。示例如下：
SELECT id, GROUP_CONCAT(COALESCE(name,'') SEPARATOR ' ')
FROM table
GROUP BY id;
Hope this helps someone else
希望这对其他人有帮助
