如何编写SQL更新语句实现两次算法测试排序结果一致

我在编写SQL更新语句时逻辑遇到困惑，具体问题如下：
我有一张名为Targets的表，表结构和样例数据如下：

a_LOG a_RF a_GNB a_SVC b_LOG b_RF b_GNB b_SVC a_1st a_2nd a_3rd a_4th b_1st b_2nd b_3rd b_4th
22    83   97    83    32    77   95    86    GNB   RF    SVC   LOG   GNB   SVC   RF    LOG

表中数据分为4个部分：

• 第一轮4种算法测试结果，对应字段如下：

a_LOG a_RF a_GNB a_SVC
22    83   97    83    

• 第二轮4种算法测试结果，对应字段如下：

b_LOG b_RF b_GNB b_SVC
32    77   95    86  

• 第一轮算法排序结果，对应字段如下：

a_1st a_2nd a_3rd a_4th
GNB   RF    SVC   LOG 

• 第二轮算法排序结果，对应字段如下：

b_1st b_2nd b_3rd b_4th
GNB   SVC   RF    LOG

现有排序规则为按算法得分降序排列，得分相同时默认按算法名称字母序排序。第一轮测试中RF和SVC得分均为83，按字母序排序RF排在第2位、SVC排在第3位，导致第一轮排序结果为GNB > RF > SVC > LOG，第二轮排序结果为GNB > SVC > RF > LOG，两者不一致。

我期望调整排序规则：第一优先级为得分降序，当两个算法得分相同时，参考另一轮测试中对应算法的排序顺序，尽可能让两轮排序结果完全一致。

我编写了可复现的测试表脚本如下：

declare @T as table(a_LOG int, a_RF int,a_GNB int,a_SVC int,b_LOG int,b_RF int,b_GNB int,b_SVC int,a_1st varchar(9), a_2nd varchar(9), a_3rd varchar(9), a_4th varchar(9), b_1st varchar(9), b_2nd varchar(9), b_3rd varchar(9), b_4th varchar(9))

insert into @T values (22, 83, 97, 83, 32, 77, 95, 86, 'GNB', 'RF', 'SVC', 'LOG', 'GNB', 'SVC', 'RF', 'LOG'),
(57, 93, 34, 67, 44, 88, 44, 82, 'RF', 'SVC', 'LOG', 'GNB', 'RF', 'SVC', 'GNB', 'LOG'),
(60, 40, 80, 90, 65, 95, 65, 95, 'SVC', 'GNB', 'LOG', 'RF', 'RF', 'SVC', 'GNB', 'LOG')

我最初尝试用case语句编写更新逻辑，但需要编写上百个case分支，可行性极低，代码如下：

update @T
set 
b_1st = case when a_LOG >= a_RF and b_LOG = b_RF then a_1st else b_1st end
             when a_LOG >= a_GNB and b_LOG = b_GNB then a_1st else b_1st end
             when a_LOG >= a_SVC and b_LOG = a_SVC then a_1st else b_1st end
             when a_LOG >= a_GNB and b_LOG = b_GNB then a_1st else b_1st end

补充说明 #

排序要求为降序排列，当两个算法得分相同时，排序顺序参考另一轮测试的对应排序，尽可能让两轮排序结果保持一致。

实现方案 #

通过宽表转窄表的方式规避大量case分支编写，核心逻辑如下：

1. 对原表每行数据，将4种算法的两轮得分、第一轮排序位次拆分为独立行，每个算法对应一行记录，同时提前计算第一轮的排序优先级（a_1st对应优先级1、a_2nd对应优先级2，以此类推）
2. 生成第二轮新排序时，排序规则设置为：首先按第二轮得分降序，得分相同的场景下按第一轮的优先级升序排列，保证同分算法的顺序和第一轮完全一致
3. 排序完成后将窄表转回宽表，直接更新原表的b_1st~b_4th字段即可

SQL Server环境下的实现代码参考：

WITH UnpivotedA AS (
    SELECT 
        %%physloc%% AS RowID,
        REPLACE(Algorithm, 'a_', '') AS PureAlg,
        AScore,
        ROW_NUMBER() OVER (PARTITION BY %%physloc%% ORDER BY 
            CASE REPLACE(Algorithm, 'a_', '')
                WHEN a_1st THEN 1 WHEN a_2nd THEN 2 WHEN a_3rd THEN 3 WHEN a_4th THEN 4 
            END) AS ARank
    FROM @T
    UNPIVOT (
        AScore FOR Algorithm IN (a_LOG, a_RF, a_GNB, a_SVC)
    ) AS ua
),
UnpivotedB AS (
    SELECT 
        %%physloc%% AS RowID,
        REPLACE(Algorithm, 'b_', '') AS PureAlg,
        BScore
    FROM @T
    UNPIVOT (
        BScore FOR Algorithm IN (b_LOG, b_RF, b_GNB, b_SVC)
    ) AS ub
),
NewBRank AS (
    SELECT 
        b.RowID,
        b.PureAlg,
        ROW_NUMBER() OVER (PARTITION BY b.RowID ORDER BY b.BScore DESC, a.ARank ASC) AS NewRank
    FROM UnpivotedB b
    JOIN UnpivotedA a ON b.RowID = a.RowID AND b.PureAlg = a.PureAlg
),
PivotedB AS (
    SELECT 
        RowID,
        [1] AS new_b1st, [2] AS new_b2nd, [3] AS new_b3rd, [4] AS new_b4th
    FROM NewBRank
    PIVOT (
        MAX(PureAlg) FOR NewRank IN ([1], [2], [3], [4])
    ) AS pvt
)
UPDATE t
SET 
    b_1st = new_b1st,
    b_2nd = new_b2nd,
    b_3rd = new_b3rd,
    b_4th = new_b4th
FROM @T t
JOIN PivotedB pb ON t.%%physloc%% = pb.RowID

该方案不需要手动枚举所有算法的两两组合，后续算法数量增加只需调整UNPIVOT的字段列表即可，扩展性极强。

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