MySQL Group 和 Concat 多列
我必须从 MySQL 数据库添加两个或多个字符串，为此我使用了 CONCAT() 函数。
这是第一个存储 PHP 类的表 classes。
class_id class_name
-------- ----------
1 accountant
2 attendance
另一个表methods 存储每个类的方法。
class_id method_name
-------- -----------------------
1 __construct
1 add_expenses
2 __construct
2 attendance_report
我编写了串联查询。
SELECT
`cc`.`class_id`,
`cc`.`class_name`,
CONCAT(`cm`.`method_name`, ',') AS `method_name`
FROM
`classes` AS `cc`
LEFT JOIN `methods` AS `cm`
ON `cm`.`class_id` = `cc`.`class_id`
GROUP BY `cc`.`class_name`;
这是行不通的。我的预期输出是
class_id class_name method_name
-------- -------------- ------------
1 accountant __construct, add_expenses, .... n
2 attendance __construct, attendance_report, .... n
有什么想法吗？
I have to add two or more strings from MySQL database, for this I used CONCAT() function.
Here is the first table classes which stores PHP classes.
class_id class_name
-------- ----------
1 accountant
2 attendance
Another table methods which stores each class methods.
class_id method_name
-------- -----------------------
1 __construct
1 add_expenses
2 __construct
2 attendance_report
And I write the query for concatenation.
SELECT
`cc`.`class_id`,
`cc`.`class_name`,
CONCAT(`cm`.`method_name`, ',') AS `method_name`
FROM
`classes` AS `cc`
LEFT JOIN `methods` AS `cm`
ON `cm`.`class_id` = `cc`.`class_id`
GROUP BY `cc`.`class_name`;
Which is not working. My expected output is
class_id class_name method_name
-------- -------------- ------------
1 accountant __construct, add_expenses, .... n
2 attendance __construct, attendance_report, .... n
Any ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
使用
GROUP_CONCAT
而不是
CONCATGROUP_CONCAT(cm.method_name)您不需要传递逗号作为默认分隔符。Use
GROUP_CONCAT
instead of
CONCATGROUP_CONCAT(cm.method_name)you do not needs to pass comma as separator that will be taken default.