在带有函数的 MySQL GROUP_CONCAT 中排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1577860/
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
Ordering in a MySQL GROUP_CONCAT with a function in it
提问by acme
I want to order the results in a GROUP_CONCAT function. The problem is, that the selection in the GROUP_CONCAT-function is another function, like this (fantasy select):
我想在 GROUP_CONCAT 函数中对结果进行排序。问题是，GROUP_CONCAT 函数中的选择是另一个函数，像这样（幻想选择）：
SELECT a.name,
GROUP_CONCAT(DISTINCT CONCAT_WS(':', b.id, c.name) ORDER BY b.id ASC) AS course
FROM people a, stuff b, courses c
GROUP BY a.id
I want to get a result like (ordered by b.id):
我想得到这样的结果（按 b.id 排序）：
michael 1:science,2:maths,3:physics
but I get:
但我得到：
michael 2:maths,1:science,3:physics
Does anyone know how I can order by b.id in my group_concat here?
有谁知道我如何在我的 group_concat 中通过 b.id 订购？
回答by Jazzy
If anyone cares, I think I found a solution for at least a similar problem.
如果有人关心，我想我至少找到了解决类似问题的方法。
http://mahmudahsan.wordpress.com/2008/08/27/mysql-the-group_concat-function/
http://mahmudahsan.wordpress.com/2008/08/27/mysql-the-group_concat-function/
select GROUP_CONCAT(columnName order by someColumn SEPARATOR '|') from tableName where fieldId = p.id
The order by goes in the group_concat BEFORE the separator if there is one.
如果有分隔符，则 order by 进入 group_concat 之前的分隔符。
回答by AMeiri
I know this is really old, but just now I was looking for an answer and @korny's answer gave me the idea for this:
我知道这真的很旧，但刚才我正在寻找答案，@korny 的答案给了我这个想法：
SELECT a.name,
GROUP_CONCAT(DISTINCT CONCAT_WS(':', b.id, c.name)
ORDER BY CONCAT_WS(':', b.id, c.name) ASC) AS course
FROM people a, stuff b, courses c
GROUP BY a.id
(And it works for me, if that wasn't clear :-) )
（它对我有用，如果不清楚:-))
回答by Sandro Frattura
No need for subselects.
不需要子选择。
SELECT people.name, CONCAT_WS(":", stuff.id, courses.name) data
FROM people, stuff, courses
ORDER BY stuff.id, people.name
回答by Luká? Lalinsky
I don't know of a standard way to do this. This query works, but I'm afraid it just depends on some implementation detail:
我不知道这样做的标准方法。这个查询有效，但恐怕它只取决于一些实现细节：
SELECT a_name, group_concat(b_id)
FROM (
SELECT a.name AS a_name, b.id AS b_id
FROM tbl1 a, tbl2 b
ORDER BY a.name, b.id) a
GROUP BY a_name
回答by korny
SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id)
FROM book_mast
GROUP BY pub_id
ORDER BY GROUP_CONCAT(DISTINCT cate_id) ASC;
回答by Izkata
SELECT generated.name, GROUP_CONCAT(generated.data)
FROM (
SELECT people.name, CONCAST_WS(":", stuff.id, courses.name) data
FROM people, stuff, courses
ORDER BY stuff.id, people.name
) generated
GROUP BY generated.name
