如何在MySQL中对单行的列值进行升序排序？

我有一张表，其中一行在指定列中存储了六个唯一数字：

如何使用MySQL将该行的列值按升序排列，得到如下结果？

我尝试过使用ORDER BY FIELD()、子查询和拼接操作，但都没有效果。我的代码如下：

SELECT aa.*, 
(SELECT CONCAT(n1,",",n2,",",n3,",",n4,",",n5,",",n6) FROM table bb WHERE bb.id=aa.id ORDER BY FIELD(n1,n2,n3,n4,n5,n6) asc) AS conc 
FROM table aa 
WHERE aa.id=1

我知道这个方法不对，但不知道如何得到正确结果。

解决方案：列转行排序后再转回列 #

你的尝试中，FIELD()函数是按你指定的固定值列表排序，而非数值大小，且直接拼接无法实现列值的重新排序分配。可以通过列转行→排序→行转列的思路解决：

方法1：兼容MySQL 5.x及以上版本 #

SELECT
    id,
    SUBSTRING_INDEX(sorted_nums, ',', 1) AS n1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(sorted_nums, ',', 2), ',', -1) AS n2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(sorted_nums, ',', 3), ',', -1) AS n3,
    SUBSTRING_INDEX(SUBSTRING_INDEX(sorted_nums, ',', 4), ',', -1) AS n4,
    SUBSTRING_INDEX(SUBSTRING_INDEX(sorted_nums, ',', 5), ',', -1) AS n5,
    SUBSTRING_INDEX(sorted_nums, ',', -1) AS n6
FROM (
    SELECT
        id,
        GROUP_CONCAT(num ORDER BY num ASC SEPARATOR ',') AS sorted_nums
    FROM (
        SELECT id, n1 AS num FROM your_table WHERE id=1
        UNION ALL
        SELECT id, n2 AS num FROM your_table WHERE id=1
        UNION ALL
        SELECT id, n3 AS num FROM your_table WHERE id=1
        UNION ALL
        SELECT id, n4 AS num FROM your_table WHERE id=1
        UNION ALL
        SELECT id, n5 AS num FROM your_table WHERE id=1
        UNION ALL
        SELECT id, n6 AS num FROM your_table WHERE id=1
    ) AS nums
    GROUP BY id
) AS sorted;

方法2：MySQL 8.0及以上版本（窗口函数实现） #

如果你的MySQL版本是8.0或更高，可以用窗口函数更简洁实现：

SELECT
    id,
    MAX(CASE WHEN rn=1 THEN num END) AS n1,
    MAX(CASE WHEN rn=2 THEN num END) AS n2,
    MAX(CASE WHEN rn=3 THEN num END) AS n3,
    MAX(CASE WHEN rn=4 THEN num END) AS n4,
    MAX(CASE WHEN rn=5 THEN num END) AS n5,
    MAX(CASE WHEN rn=6 THEN num END) AS n6
FROM (
    SELECT
        id,
        num,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY num ASC) AS rn
    FROM (
        SELECT id, n1 AS num FROM your_table WHERE id=1
        UNION ALL
        SELECT id, n2 AS num FROM your_table WHERE id=1
        UNION ALL
        SELECT id, n3 AS num FROM your_table WHERE id=1
        UNION ALL
        SELECT id, n4 AS num FROM your_table WHERE id=1
        UNION ALL
        SELECT id, n5 AS num FROM your_table WHERE id=1
        UNION ALL
        SELECT id, n6 AS num FROM your_table WHERE id=1
    ) AS nums
) AS ranked
GROUP BY id;

逻辑说明 #

1. 列转行：通过UNION ALL将每一列的数值拆分为单独行，每个行对应一个数值；
2. 排序：方法1用GROUP_CONCAT直接拼接成升序排列的字符串，方法2用ROW_NUMBER()给排序后的数值分配序号；
3. 行转列：方法1用SUBSTRING_INDEX截取字符串对应部分，方法2用CASE结合聚合函数，按序号将数值分配到对应列。

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