MySQL如何按列值降序拼接列名生成新列？

现有MySQL表结构及数据如下：

A	B	C	D	E	F
12	23	15	17	9	21
9	13	15	19	4	2
26	11	6	3	15	21

需要新增code列，值为按各列值降序排列的6个列名拼接结果，示例结果：

code
BFDCAE
DCBAEF
AFEBCD

尝试用GREATEST()和LEAST()实现需大量代码，求简便方法。

核心思路 #

将每行的列名与对应值转换为行级数据，利用GROUP_CONCAT()函数配合排序规则，直接拼接降序排列后的列名，无需逐个判断极值。

具体实现 #

假设表名为your_table，若表有主键（如id），可使用以下SQL生成code列的值：

-- 生成对应code值的查询语句
SELECT 
    t.id,
    GROUP_CONCAT(t2.col_name ORDER BY t2.col_val DESC SEPARATOR '') AS code
FROM your_table t
JOIN (
    SELECT id, 'A' AS col_name, A AS col_val FROM your_table
    UNION ALL
    SELECT id, 'B', B FROM your_table
    UNION ALL
    SELECT id, 'C', C FROM your_table
    UNION ALL
    SELECT id, 'D', D FROM your_table
    UNION ALL
    SELECT id, 'E', E FROM your_table
    UNION ALL
    SELECT id, 'F', F FROM your_table
) t2 ON t.id = t2.id
GROUP BY t.id;

若表无主键，可通过临时变量生成行标识来关联：

SELECT 
    t1.temp_id,
    GROUP_CONCAT(t2.col_name ORDER BY t2.col_val DESC SEPARATOR '') AS code
FROM (
    SELECT 
        @row_num := @row_num + 1 AS temp_id,
        A, B, C, D, E, F
    FROM your_table, (SELECT @row_num := 0) rn
) t1
JOIN (
    SELECT temp_id, 'A' AS col_name, A AS col_val FROM t1
    UNION ALL
    SELECT temp_id, 'B', B FROM t1
    UNION ALL
    SELECT temp_id, 'C', C FROM t1
    UNION ALL
    SELECT temp_id, 'D', D FROM t1
    UNION ALL
    SELECT temp_id, 'E', E FROM t1
    UNION ALL
    SELECT temp_id, 'F', F FROM t1
) t2 ON t1.temp_id = t2.temp_id
GROUP BY t1.temp_id;

新增并更新code列 #

若要直接在原表中新增code列并填充值，可执行以下步骤：

-- 1. 新增code列
ALTER TABLE your_table ADD COLUMN code VARCHAR(6) NOT NULL DEFAULT '';

-- 2. 更新code列的值（假设表有主键id）
UPDATE your_table t
JOIN (
    SELECT 
        id,
        GROUP_CONCAT(col_name ORDER BY col_val DESC SEPARATOR '') AS code_val
    FROM (
        SELECT id, 'A' AS col_name, A AS col_val FROM your_table
        UNION ALL
        SELECT id, 'B', B FROM your_table
        UNION ALL
        SELECT id, 'C', C FROM your_table
        UNION ALL
        SELECT id, 'D', D FROM your_table
        UNION ALL
        SELECT id, 'E', E FROM your_table
        UNION ALL
        SELECT id, 'F', F FROM your_table
    ) t2
    GROUP BY id
) t3 ON t.id = t3.id
SET t.code = t3.code_val;

结果验证 #

执行上述语句后，表中code列的值将完全符合示例要求：

• 第一行：BFDCAE（对应值排序：23(B) > 21(F) > 17(D) > 15(C) > 12(A) > 9(E)）
• 第二行：DCBAEF（对应值排序：19(D) > 15(C) > 13(B) > 9(A) > 4(E) > 2(F)）
• 第三行：AFEBCD（对应值排序：26(A) > 21(F) > 15(E) > 11(B) > 6(C) > 3(D)）

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