我可以将多个 MySQL 行连接到一个字段中吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/276927/
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
Can I concatenate multiple MySQL rows into one field?
提问by Dean Rather
Using MySQL, I can do something like:
使用MySQL，我可以执行以下操作：
SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;
My Output:
我的输出：
shopping
fishing
coding
but instead I just want 1 row, 1 col:
但相反，我只想要 1 行，1 列：
Expected Output:
预期输出：
shopping, fishing, coding
The reason is that I'm selecting multiple values from multiple tables, and after all the joins I've got a lot more rows than I'd like.
原因是我从多个表中选择多个值，并且在所有连接之后，我的行比我想要的多得多。
I've looked for a function on MySQL Docand it doesn't look like the CONCATor CONCAT_WSfunctions accept result sets.
我在MySQL Doc上寻找了一个函数，它看起来不像CONCATorCONCAT_WS函数接受结果集。
So does anyone here know how to do this?
那么这里有人知道如何做到这一点吗？
回答by che
You can use GROUP_CONCAT:
您可以使用GROUP_CONCAT：
SELECT person_id, GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Ludwig stated in his comment,you can add the DISTINCToperator to avoid duplicates:
正如 Ludwig 在他的评论中所说，您可以添加DISTINCT运算符以避免重复：
SELECT person_id, GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Jan stated in their comment,you can also sort the values before imploding it using ORDER BY:
正如 Jan 在他们的评论中所述，您还可以在使用ORDER BY以下方法对值进行内爆之前对其进行排序：
SELECT person_id, GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Dag stated in his comment,there is a 1024 byte limit on the result. To solve this, run this query before your query:
正如 Dag 在他的评论中所说，结果有 1024 字节的限制。要解决此问题，请在查询之前运行此查询：
SET group_concat_max_len = 2048;
Of course, you can change 2048according to your needs. To calculate and assign the value:
当然，您可以2048根据自己的需要进行更改。要计算和分配值：
SET group_concat_max_len = CAST(
(SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
FROM peoples_hobbies
GROUP BY person_id)
AS UNSIGNED
);
回答by lpfavreau
Have a look at GROUP_CONCATif your MySQL version (4.1) supports it. See the documentationfor more details.
看看GROUP_CONCAT您的 MySQL 版本 (4.1) 是否支持它。有关更多详细信息，请参阅文档。
It would look something like:
它看起来像：
SELECT GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
WHERE person_id = 5
GROUP BY 'all';
回答by elbowlobstercowstand
Alternate syntax to concatenate multiple, individual rows
连接多个单行的替代语法
WARNING: This post will make you hungry.
警告：这篇文章会让你感到饥饿。
Given:
鉴于：
I found myself wanting to select multiple, individual rows—instead of a group—and concatenate on a certain field.
我发现自己想要选择多个单独的行——而不是一个组——并连接到某个字段上。
Let's say you have a table of product ids and their names and prices:
假设您有一个产品 ID 及其名称和价格表：
+------------+--------------------+-------+
| product_id | name | price |
+------------+--------------------+-------+
| 13 | Double Double | 5 |
| 14 | Neapolitan Shake | 2 |
| 15 | Animal Style Fries | 3 |
| 16 | Root Beer | 2 |
| 17 | Lame T-Shirt | 15 |
+------------+--------------------+-------+
Then you have some fancy-schmancy ajax that lists these puppies off as checkboxes.
然后你有一些花哨的 schmancy ajax，将这些小狗列为复选框。
Your hungry-hippo user selects 13, 15, 16. No dessert for her today...
您的饥饿河马用户选择13, 15, 16。今天没有给她甜点...
Find:
找：
A way to summarize your user's order in one line, with pure mysql.
一种用纯mysql在一行中总结用户订单的方法。
Solution:
解决方案：
Use GROUP_CONCATwith the the INclause:
使用GROUP_CONCAT与该IN条款：
mysql> SELECT GROUP_CONCAT(name SEPARATOR ' + ') AS order_summary FROM product WHERE product_id IN (13, 15, 16);
Which outputs:
哪些输出：
+------------------------------------------------+
| order_summary |
+------------------------------------------------+
| Double Double + Animal Style Fries + Root Beer |
+------------------------------------------------+
Bonus Solution:
奖金解决方案：
If you want the total price too, toss in SUM():
如果您也想要总价，请投入SUM()：
mysql> SELECT GROUP_CONCAT(name SEPARATOR ' + ') AS order_summary, SUM(price) AS total FROM product WHERE product_id IN (13, 15, 16);
+------------------------------------------------+-------+
| order_summary | total |
+------------------------------------------------+-------+
| Double Double + Animal Style Fries + Root Beer | 10 |
+------------------------------------------------+-------+
PS: Apologies if you don't have an In-N-Outnearby...
PS：抱歉，如果您附近没有In-N-Out...
回答by pau.moreno
You can change the max length of the GROUP_CONCATvalue by setting the group_concat_max_lenparameter.
您可以GROUP_CONCAT通过设置group_concat_max_len参数来更改值的最大长度。
See details in the MySQL documantation.
请参阅MySQL 文档中的详细信息。
回答by Dean Rather
There's a GROUP Aggregate function, GROUP_CONCAT.
有一个 GROUP 聚合函数GROUP_CONCAT。
回答by Fedir RYKHTIK
In my case I had a row of Ids, and it was neccessary to cast it to char, otherwise, the result was encoded into binary format :
就我而言，我有一行 Id，必须将其转换为 char，否则，结果将被编码为二进制格式：
SELECT CAST(GROUP_CONCAT(field SEPARATOR ',') AS CHAR) FROM table
回答by Shen liang
Use MySQL(5.6.13) session variable and assignment operator like the following
使用 MySQL(5.6.13) 会话变量和赋值运算符，如下所示
SELECT @logmsg := CONCAT_ws(',',@logmsg,items) FROM temp_SplitFields a;
then you can get
然后你可以得到
test1,test11
回答by Alex Bowyer
I had a more complicated query, and found that I had to use GROUP_CONCATin an outer query to get it to work:
我有一个更复杂的查询，发现我必须GROUP_CONCAT在外部查询中使用才能使其工作：
Original Query:
原始查询：
SELECT DISTINCT userID
FROM event GROUP BY userID
HAVING count(distinct(cohort))=2);
Imploded:
内爆：
SELECT GROUP_CONCAT(sub.userID SEPARATOR ', ')
FROM (SELECT DISTINCT userID FROM event
GROUP BY userID HAVING count(distinct(cohort))=2) as sub;
Hope this might help someone.
希望这可以帮助某人。
回答by Oleg Abrazhaev
For somebody looking here how to use GROUP_CONCATwith subquery - posting this example
对于在这里查看如何使用GROUP_CONCAT子查询的人 - 发布此示例
SELECT i.*,
(SELECT GROUP_CONCAT(userid) FROM favourites f WHERE f.itemid = i.id) AS idlist
FROM items i
WHERE i.id = $someid
So GROUP_CONCATmust be used inside the subquery, not wrapping it.
所以GROUP_CONCAT必须在子查询内部使用，而不是包装它。
回答by thejustv
Try this:
尝试这个：
DECLARE @Hobbies NVARCHAR(200) = ' '
SELECT @Hobbies = @Hobbies + hobbies + ',' FROM peoples_hobbies WHERE person_id = 5;
