如何为三类字母评级赋值排序并生成最小评级新列？

SQL 实现方法 #

不需要新增中间列，通过嵌套CASE表达式完成「评级转排名→取最小排名→转回统一评级」的流程，直接生成目标列：

简化写法（重复计算排名） #

SELECT
    Rating_1,
    Rating_2,
    Rating_3,
    CASE
        WHEN LEAST(
            CASE Rating_1 WHEN 'A+' THEN 3 WHEN 'B+' THEN 2 WHEN 'Fa' THEN 1 END,
            CASE Rating_2 WHEN 'AA' THEN 3 WHEN 'BB' THEN 2 WHEN 'Fb' THEN 1 END,
            CASE Rating_3 WHEN 'Aa' THEN 3 WHEN 'Bb' THEN 2 WHEN 'Fc' THEN 1 END
        ) = 1 THEN 'Fa'
        WHEN LEAST(
            CASE Rating_1 WHEN 'A+' THEN 3 WHEN 'B+' THEN 2 WHEN 'Fa' THEN 1 END,
            CASE Rating_2 WHEN 'AA' THEN 3 WHEN 'BB' THEN 2 WHEN 'Fb' THEN 1 END,
            CASE Rating_3 WHEN 'Aa' THEN 3 WHEN 'Bb' THEN 2 WHEN 'Fc' THEN 1 END
        ) = 2 THEN 'B+'
        WHEN LEAST(
            CASE Rating_1 WHEN 'A+' THEN 3 WHEN 'B+' THEN 2 WHEN 'Fa' THEN 1 END,
            CASE Rating_2 WHEN 'AA' THEN 3 WHEN 'BB' THEN 2 WHEN 'Fb' THEN 1 END,
            CASE Rating_3 WHEN 'Aa' THEN 3 WHEN 'Bb' THEN 2 WHEN 'Fc' THEN 1 END
        ) = 3 THEN 'A+'
    END AS Min_Rating
FROM your_table;

优化写法（避免重复计算） #

如果担心重复计算影响性能，可用子查询先算出最小排名，再转换评级：

SELECT
    Rating_1,
    Rating_2,
    Rating_3,
    CASE min_rank
        WHEN 1 THEN 'Fa'
        WHEN 2 THEN 'B+'
        WHEN 3 THEN 'A+'
    END AS Min_Rating
FROM (
    SELECT
        Rating_1,
        Rating_2,
        Rating_3,
        LEAST(
            CASE Rating_1 WHEN 'A+' THEN 3 WHEN 'B+' THEN 2 WHEN 'Fa' THEN 1 END,
            CASE Rating_2 WHEN 'AA' THEN 3 WHEN 'BB' THEN 2 WHEN 'Fb' THEN 1 END,
            CASE Rating_3 WHEN 'Aa' THEN 3 WHEN 'Bb' THEN 2 WHEN 'Fc' THEN 1 END
        ) AS min_rank
    FROM your_table
) t;

Excel 实现方法 #

直接在目标单元格输入嵌套函数，无需新增中间列。假设评级数据在A2、B2、C2单元格，在D2单元格输入以下公式：

用LOOKUP函数（兼容所有Excel版本） #

=LOOKUP(MIN(
    LOOKUP(A2,{"A+","B+","Fa"},{3,2,1}),
    LOOKUP(B2,{"AA","BB","Fb"},{3,2,1}),
    LOOKUP(C2,{"Aa","Bb","Fc"},{3,2,1})
),{1,2,3},{"Fa","B+","A+"})

用XLOOKUP函数（适用于Excel 365/2021及以上版本） #

=XLOOKUP(MIN(
    XLOOKUP(A2,{"A+","B+","Fa"},{3,2,1}),
    XLOOKUP(B2,{"AA","BB","Fb"},{3,2,1}),
    XLOOKUP(C2,{"Aa","Bb","Fc"},{3,2,1})
),{1,2,3},{"Fa","B+","A+"})

公式逻辑：先把每个评级转成对应排名，取最小排名后，再转回统一的目标评级。

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