对 GROUP_CONCAT 语句中的值进行排序

在 MySQL 中，GROUP_CONCAT 根据特定分组组合来自一列的值。然而，连接值的顺序通常由分组发生的顺序确定。要控制这些值的排序，需要特定的语法。

要对 GROUP_CONCAT 语句中的串联值进行排序，请在嵌套查询中使用 ORDER BY 子句。例如，考虑以下查询：

GROUP_CONCAT((SELECT GROUP_CONCAT(parent.name SEPARATOR " » ") 
FROM test_competence AS node, test_competence AS parent 
WHERE node.lft BETWEEN parent.lft AND parent.rgt 
  AND node.id = l.competence 
  AND parent.id != 1 
ORDER BY parent.lft) SEPARATOR "<br />\n") AS competences

要按升序对连接的能力值进行排序，请修改查询如下：

GROUP_CONCAT((SELECT GROUP_CONCAT(parent.name SEPARATOR " » ") 
FROM test_competence AS node, test_competence AS parent 
WHERE node.lft BETWEEN parent.lft AND parent.rgt 
  AND node.id = l.competence 
  AND parent.id != 1 
ORDER BY parent.lft ASC) SEPARATOR "<br />\n") AS competences

同样，要按降序对值进行排序，请在 ASC 后使用 DESC 关键字：

GROUP_CONCAT((SELECT GROUP_CONCAT(parent.name SEPARATOR " » ") 
FROM test_competence AS node, test_competence AS parent 
WHERE node.lft BETWEEN parent.lft AND parent.rgt 
  AND node.id = l.competence 
  AND parent.id != 1 
ORDER BY parent.lft DESC) SEPARATOR "<br />\n") AS competences