多列rating对比取第二优并自动更新的SQL实现方案咨询

• 第一步：建立评级优先级映射
  先对照rating_reference的评级优劣顺序，生成评级文本-优先级数值的对应关系，评级越优对应数值越大。比如AAA对应10、AA+对应9、AA对应8，以此类推，最低评级对应最小数值。
• 第二步：行转列处理多列评级
  把AutoFind_Rating表每行的A、B、C、D、E五列转为行数据，过滤掉空值，关联优先级映射表给每个非空评级赋值优先级。
• 第三步：计算第二优评级并更新
  对每行的非空评级按优先级降序排序，取排名第二的评级作为结果。如果五列全为空则赋值为'unrated'；如果仅存在1个非空评级，可根据业务需求选择直接取该唯一值或自定义其他规则，最终将结果写入rating列。

-- 1. 创建临时优先级映射表，需根据实际rating_reference调整对应数值
CREATE TEMPORARY TABLE rating_priority (
    rating_val VARCHAR(10) PRIMARY KEY,
    priority INT NOT NULL
);
-- 示例映射，替换为你的实际评级规则
INSERT INTO rating_priority VALUES 
('AAA',10),('AA+',9),('AA',8),('AA-',7),('A+',6),('A',5),('A-',4),
('BBB',3),('BB',2),('B',1),('CCC',0),('CC',-1),('C',-2),('D',-3);

-- 2. 执行rating列更新，假设表主键为id
UPDATE AutoFind_Rating t
LEFT JOIN (
    SELECT 
        id,
        SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(rating_val ORDER BY priority DESC), ',', 2), ',', -1) AS second_best,
        COUNT(*) AS not_null_count
    FROM (
        SELECT id, A AS rating_val FROM AutoFind_Rating WHERE A IS NOT NULL
        UNION ALL
        SELECT id, B AS rating_val FROM AutoFind_Rating WHERE B IS NOT NULL
        UNION ALL
        SELECT id, C AS rating_val FROM AutoFind_Rating WHERE C IS NOT NULL
        UNION ALL
        SELECT id, D AS rating_val FROM AutoFind_Rating WHERE D IS NOT NULL
        UNION ALL
        SELECT id, E AS rating_val FROM AutoFind_Rating WHERE E IS NOT NULL
    ) row_data
    JOIN rating_priority p ON row_data.rating_val = p.rating_val
    GROUP BY id
) calc_result ON t.id = calc_result.id
SET t.rating = CASE 
    WHEN calc_result.not_null_count IS NULL THEN 'unrated'
    WHEN calc_result.not_null_count = 1 THEN calc_result.second_best
    ELSE calc_result.second_best
END;

• 若使用SQL Server、PostgreSQL等数据库，可使用原生UNPIVOT语法替代多个UNION ALL，执行效率更高
• 优先级映射的数值必须和rating_reference的规则完全匹配，否则会导致排序结果错误
• 执行UPDATE操作前建议先运行子查询验证计算结果，或备份原始数据避免误操作

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