SQL Server如何按列值排序将对应列名填入a_1st至a_4th列

核心思路 #

将每行需要排序的a_LOG、a_RF、a_GNB、a_SVC四列拆为键值对行，按值降序+列名升序排名后，再行转列回填到a_1st至a_4th字段，无需编写复杂嵌套的判断逻辑。

实现代码 #

SQL Server #

WITH RankedColumns AS (
    SELECT 
        t.RowCnt, t.Lvl, t.a_TargetID, t.b_TargetID,
        v.col_name,
        ROW_NUMBER() OVER (
            PARTITION BY t.RowCnt, t.Lvl, t.a_TargetID, t.b_TargetID 
            ORDER BY v.col_value DESC, v.col_name ASC
        ) AS rank_num
    FROM Targets t
    CROSS APPLY (
        VALUES 
            ('a_LOG', a_LOG), 
            ('a_RF', a_RF), 
            ('a_GNB', a_GNB), 
            ('a_SVC', a_SVC)
    ) v(col_name, col_value)
)
UPDATE t
SET 
    a_1st = MAX(CASE WHEN rc.rank_num = 1 THEN rc.col_name END),
    a_2nd = MAX(CASE WHEN rc.rank_num = 2 THEN rc.col_name END),
    a_3rd = MAX(CASE WHEN rc.rank_num = 3 THEN rc.col_name END),
    a_4th = MAX(CASE WHEN rc.rank_num = 4 THEN rc.col_name END)
FROM Targets t
INNER JOIN RankedColumns rc 
    ON t.RowCnt = rc.RowCnt 
    AND t.Lvl = rc.Lvl 
    AND t.a_TargetID = rc.a_TargetID 
    AND t.b_TargetID = rc.b_TargetID
GROUP BY t.RowCnt, t.Lvl, t.a_TargetID, t.b_TargetID, t.a_1st, t.a_2nd, t.a_3rd, t.a_4th

MySQL 8.0+/PostgreSQL #

WITH RankedColumns AS (
    SELECT 
        RowCnt, Lvl, a_TargetID, b_TargetID,
        'a_LOG' AS col_name, a_LOG AS col_value FROM Targets
    UNION ALL
    SELECT RowCnt, Lvl, a_TargetID, b_TargetID, 'a_RF' AS col_name, a_RF AS col_value FROM Targets
    UNION ALL
    SELECT RowCnt, Lvl, a_TargetID, b_TargetID, 'a_GNB' AS col_name, a_GNB AS col_value FROM Targets
    UNION ALL
    SELECT RowCnt, Lvl, a_TargetID, b_TargetID, 'a_SVC' AS col_name, a_SVC AS col_value FROM Targets
),
Ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY RowCnt, Lvl, a_TargetID, b_TargetID 
            ORDER BY col_value DESC, col_name ASC
        ) AS rank_num
    FROM RankedColumns
)
UPDATE Targets t
JOIN (
    SELECT 
        RowCnt, Lvl, a_TargetID, b_TargetID,
        MAX(CASE WHEN rank_num = 1 THEN col_name END) AS r1,
        MAX(CASE WHEN rank_num = 2 THEN col_name END) AS r2,
        MAX(CASE WHEN rank_num = 3 THEN col_name END) AS r3,
        MAX(CASE WHEN rank_num = 4 THEN col_name END) AS r4
    FROM Ranked
    GROUP BY RowCnt, Lvl, a_TargetID, b_TargetID
) tmp
    ON t.RowCnt = tmp.RowCnt 
    AND t.Lvl = tmp.Lvl 
    AND t.a_TargetID = tmp.a_TargetID 
    AND t.b_TargetID = tmp.b_TargetID
SET t.a_1st = tmp.r1, t.a_2nd = tmp.r2, t.a_3rd = tmp.r3, t.a_4th = tmp.r4

方案优势 #

• 无需编写复杂嵌套的CASE判断逻辑，代码可读性强
• 扩展性高，后续新增需要排序的指标列，仅需在键值对生成部分新增对应条目即可
• 支持自定义同值排序规则，示例中同值按列名字母序排序，完全匹配预期结果

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