MySQL：GROUP_CONCAT使用时外层ORDER BY失效的解决方法

数据表结构 #

table
+--------------------+-------+
| id      |  name    |  age  |
+--------------------+-------+
| 1       | client1  |  10   |
| 2       | client2  |  20   |
| 3       | client3  |  30   |
| 4       | client4  |  40   |
+--------------------+-------+

需求 #

查询返回按id降序排列后的第一行的id、age、name字段，同时返回除该行外所有行的id组成的逗号分隔字符串，预期输出：

4, 40, client4, "3,2,1"

尝试的错误SQL及结果 #

尝试执行以下语句：

SELECT id, age, name, SUBSTRING(GROUP_CONCAT(id), POSITION("," IN GROUP_CONCAT(id ORDER BY id DESC))+1)  as previous_ids
FROM table 
ORDER BY id DESC;

但结果不符合预期，外层ORDER BY未生效，返回：

1, 1, client1, "3,2,1"

问题原因 #

原SQL未使用GROUP BY却调用了GROUP_CONCAT，数据库会将整张表视为一个分组，此时SELECT中的id、age、name会取分组内的任意一行数据（通常是表中物理顺序的第一行），外层ORDER BY仅对分组后的单行结果排序，无法改变返回的行内容，因此得不到目标行。

解决方案 #

方法一：子查询直接定位目标行（兼容多数数据库） #

通过子查询获取最大id的行，同时拼接所有小于该id的记录并按降序排列：

SELECT 
    t.id, 
    t.age, 
    t.name, 
    (SELECT GROUP_CONCAT(id ORDER BY id DESC) FROM table WHERE id < t.id) AS previous_ids
FROM table t
WHERE t.id = (SELECT MAX(id) FROM table);

方法二：窗口函数实现（适用于MySQL 8.0+、PostgreSQL等支持窗口函数的数据库） #

先通过窗口函数标记降序后的第一行，再生成所有id的拼接字符串并剔除首项：

WITH ranked_data AS (
    SELECT 
        id, 
        age, 
        name,
        ROW_NUMBER() OVER(ORDER BY id DESC) AS rn,
        GROUP_CONCAT(id ORDER BY id DESC) OVER() AS all_ids
    FROM table
)
SELECT 
    id, 
    age, 
    name,
    SUBSTRING_INDEX(all_ids, ',', LENGTH(all_ids) - LENGTH(REPLACE(all_ids, ',', ''))) AS previous_ids
FROM ranked_data
WHERE rn = 1;

内容的提问来源于stack exchange，提问作者Shvalb