對 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