如何在SQL中保留每行列间最大值并将其余列值置为0？

我有一个包含cat1、cat2、cat3、cat4四个整数列的表，各列存储不同分类值。需要实现：找出每行中这四列的最大值，仅保留该最大值，其余列的值置为0。

可复现示例代码 #

CREATE TABLE #df (
    cat1 int,
    cat2 int,
    cat3 int,
    cat4 int
);

INSERT INTO #df
(
    cat1,
    cat2,
    cat3,
    cat4
)
VALUES
(   1, 0, 3, 4 ),
(   0, 2, 0, 4 ),
(   1, 2, 0, 0 ),
(   0, 0, 0, 4 )

SELECT * FROM #df

期望结果 #

我的尝试 #

我写出了能生成包含最大值的新列的代码，但希望保留原有4列，仅将非最大值的列值置为0：

SELECT Cat1, Cat2, Cat3, Cat4, 
    (SELECT Max(Col) FROM (VALUES (Cat1), (Cat2), (Cat3), (Cat4)) AS X(Col)) AS TheMax
FROM #df

解决方案 #

可以通过CASE语句结合每行的最大值判断来实现，先计算每行的最大值，再对每列判断是否等于该最大值，满足条件则保留原值，否则置为0：

SELECT
    CASE WHEN cat1 = TheMax THEN cat1 ELSE 0 END AS cat1,
    CASE WHEN cat2 = TheMax THEN cat2 ELSE 0 END AS cat2,
    CASE WHEN cat3 = TheMax THEN cat3 ELSE 0 END AS cat3,
    CASE WHEN cat4 = TheMax THEN cat4 ELSE 0 END AS cat4
FROM (
    SELECT 
        cat1, cat2, cat3, cat4,
        (SELECT Max(Col) FROM (VALUES (cat1), (cat2), (cat3), (cat4)) AS X(Col)) AS TheMax
    FROM #df
) AS temp

说明 #

• 内层子查询先计算出每行的最大值TheMax；
• 外层查询用CASE语句逐一判断每列值是否等于该行最大值，满足条件则保留原数值，否则替换为0；
• 该方案适配SQL Server（示例使用了SQL Server临时表语法），逻辑清晰且易于扩展。

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