在 MySQL GROUP_CONCAT 中使用函数进行排序

发布于 2024-08-08 04:27:18 字数 468 浏览 8 评论 0原文

我想在 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

我想要得到像（按 b.id 排序）这样的结果：

michael    1:science,2:maths,3:physics

但我得到：

michael    2:maths,1:science,3:physics

有谁知道我如何排序b.id 在我的 group_concat 中吗？

原文

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):

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):

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

埖埖迣鎅 2024-08-15 04:27:18

如果有人关心的话，我想我至少找到了类似问题的解决方案。

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

如果有分隔符，则 order by 进入 group_concat 之前的分隔符。

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/

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.

回复收藏 0 原文

何以笙箫默 2024-08-15 04:27:18

我知道这确实很老了，但现在我正在寻找答案，@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

如果不清楚的话，它对我有用:-)）

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:

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 :-) )

回复收藏 0 原文

等往事风中吹 2024-08-15 04:27:18

我不知道执行此操作的标准方法。这个查询有效，但恐怕它只取决于一些实现细节：

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

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

回复收藏 0 原文

慵挽 2024-08-15 04:27:18

不需要子选择。

SELECT people.name, CONCAT_WS(":", stuff.id, courses.name) data
FROM people, stuff, courses
ORDER BY stuff.id, people.name

No need for subselects.

SELECT people.name, CONCAT_WS(":", stuff.id, courses.name) data
FROM people, stuff, courses
ORDER BY stuff.id, people.name

回复收藏 0 原文

眼眸里的快感 2024-08-15 04:27:18

SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id)
FROM book_mast
GROUP BY pub_id
ORDER BY GROUP_CONCAT(DISTINCT cate_id) ASC;

SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id)
FROM book_mast
GROUP BY pub_id
ORDER BY GROUP_CONCAT(DISTINCT cate_id) ASC;

回复收藏 0 原文

我的影子我的梦 2024-08-15 04:27:18

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

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

回复收藏 0 原文

~没有更多了~