SQL中如何获取每一行内数值最大的条目对应的列名？

核心思路 #

• 字段数量不固定/较多时：优先把宽表转长表（行转列），按id分组匹配最大值对应的列名，兼容性最高
• 字段数量固定且较少时：直接用条件判断/内置函数计算，性能更优
  注意：如果同一行有多个字段数值相同且均为最大值，以下默认返回第一个匹配的列，如需返回所有最大值列可自行调整逻辑

各数据库实现示例 #

MySQL #

8.0.19及以上版本（支持任意数量字段）

WITH unpivoted_data AS (
    SELECT 
        id,
        col_name,
        col_value
    FROM 你的表名
    CROSS JOIN JSON_TABLE(
        JSON_OBJECT('col1', col1, 'col2', col2, 'col3', col3), -- 有更多字段直接在JSON_OBJECT里追加即可
        '$.*' COLUMNS (
            col_name VARCHAR(64) PATH '$."key"',
            col_value INT PATH '$."value"'
        )
    ) AS jt
)
SELECT 
    u.id,
    u.col_name AS max_value_col
FROM unpivoted_data u
INNER JOIN (
    SELECT id, MAX(col_value) AS max_val
    FROM unpivoted_data
    GROUP BY id
) max_cal ON u.id = max_cal.id AND u.col_value = max_cal.max_val;

低版本/字段固定场景

SELECT
    id,
    CASE 
        WHEN col1 >= col2 AND col1 >= col3 THEN 'col1'
        WHEN col2 >= col1 AND col2 >= col3 THEN 'col2'
        ELSE 'col3'
    END AS max_value_col
FROM 你的表名;

PostgreSQL #

SELECT
    id,
    (ARRAY['col1', 'col2', 'col3'])[array_position(ARRAY[col1, col2, col3], GREATEST(col1, col2, col3))] AS max_value_col
FROM 你的表名;

SQL Server #

SELECT
    t.id,
    m.col_name AS max_value_col
FROM 你的表名 t
CROSS APPLY (
    SELECT TOP 1 col_name
    FROM (VALUES ('col1', col1), ('col2', col2), ('col3', col3)) AS x(col_name, col_value)
    ORDER BY col_value DESC
) m;

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