如何将子查询中的字符串连接成 mysql 中的一行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3935695/
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 do I concatenate strings from a subquery into a single row in mysql?
提问by mrbinky3000
I have three tables:
我有三张表：
table "package"
-----------------------------------------------------
package_id int(10) primary key, auto-increment
package_name varchar(255)
price decimal(10,2)
table "zones"
------------------------------------------------------
zone_id varchar(32) primary key (ex of data: A1, Z2, E3, etc)
table "package_zones"
------------------------------------------------------
package_id int(10)
zone_id varchar(32)
What I'm trying to do is return all the information in package table PLUS a list of zones for that package. I want the list of zones sorted alphabetically and comma separated.
我想要做的是返回包表中的所有信息以及该包的区域列表。我想要按字母顺序排序并以逗号分隔的区域列表。
So the output I'm looking for is something like this...
所以我正在寻找的输出是这样的......
+------------+---------------+--------+----------------+
| package_id | package_name | price | zone_list |
+------------+---------------+--------+----------------+
| 1 | Red Package | 50.00 | Z1,Z2,Z3 |
| 2 | Blue Package | 75.00 | A2,D4,Z1,Z2 |
| 3 | Green Package | 100.00 | B4,D1,D2,X1,Z1 |
+------------+---------------+--------+----------------+
I know I could do something in PHP with the presentation layer to get the desired result. The problem is, I would like to be able to sort zone_list ASC or DESC or even use" WHERE zone_list LIKE" and so on. In order to do that, I need this done in MYSQL.
我知道我可以在 PHP 中用表示层做一些事情来获得想要的结果。问题是，我希望能够对 zone_list ASC 或 DESC 进行排序，甚至可以使用“WHERE zone_list LIKE”等等。为了做到这一点，我需要在 MYSQL 中完成。
I have NO idea how to even begin to tackle this. I tried using a subquery, but it kept complaining about multiple rows. I tried to concat the multiple rows into a single string, but evidently MySQL doesn't like this.
我什至不知道如何开始解决这个问题。我尝试使用子查询，但它一直抱怨多行。我试图将多行连接成一个字符串，但显然 MySQL 不喜欢这样。
Thanks in advance.
提前致谢。
UPDATE!
更新！
Here is the solution for those who are interested.
这是为那些有兴趣的人提供的解决方案。
SELECT
`package`.*,
GROUP_CONCAT(`zones`.`zone` ORDER BY `zones`.`zone` ASC SEPARATOR ',' ) as `zone_list`
FROM
`package`,
`package_zones`
LEFT JOIN
(`zones`,`ao_package_zones`) ON (`zones`.`zone_id` = `package_zones`.`zone_id` AND `package_zones`.`package_id` = `package`.`package_id`)
GROUP BY
`ao_package`.`package_id`
回答by GSto
by using the GROUP_CONCAT()function and a GROUP BY call. here's an example query
通过使用GROUP_CONCAT()函数和 GROUP BY 调用。这是一个示例查询
SELECT
p.package_id,
p.package_name,
p.price,
GROUP_CONCAT(pz.zone_id SEPARATOR ',') as zone_list
FROM
package p
LEFT JOIN package_zone pz ON p.package_id = pz.package_id
GROUP BY
p.package_id
you should still be able to order by zone_id s (or zone_list), and instead of using LIKE, you can use WHERE zp.zone_id = 'Z1'or something similar.
您应该仍然可以按 zone_id s（或 zone_list）排序，并且LIKE可以使用WHERE zp.zone_id = 'Z1'或类似的东西代替使用。
