基于列优先级保留唯一列值为1的更优SQL实现方案咨询

需求明确：按Valid→Expired→NA→WIP的优先级，每条数据仅保留最高优先级且值为1的列，其余列置为0。以下是两种比复杂CASE语句更简洁高效的实现方案：

方案一：顺序优先级判断法 #

直接按优先级顺序依次判断，只有当前面的高优先级列都为0时，才检查当前列是否为1，写法直观易读：

SELECT
    ID,
    CASE WHEN Valid = 1 THEN 1 ELSE 0 END AS Valid,
    CASE WHEN Valid = 0 AND Expired = 1 THEN 1 ELSE 0 END AS Expired,
    CASE WHEN Valid = 0 AND Expired = 0 AND NA = 1 THEN 1 ELSE 0 END AS NA,
    CASE WHEN Valid = 0 AND Expired = 0 AND NA = 0 AND WIP = 1 THEN 1 ELSE 0 END AS WIP
FROM your_table;

方案二：优先级标记法 #

先给每条记录标记出最高优先级的状态，再根据标记生成结果列，后续调整优先级时只需修改标记逻辑，维护成本更低：

WITH ranked_status AS (
    SELECT
        ID,
        Valid,
        Expired,
        NA,
        WIP,
        -- 给各状态分配优先级数值，数值越小优先级越高
        COALESCE(
            CASE WHEN Valid = 1 THEN 1 END,
            CASE WHEN Expired = 1 THEN 2 END,
            CASE WHEN NA = 1 THEN 3 END,
            CASE WHEN WIP = 1 THEN 4 END
        ) AS top_priority
    FROM your_table
)
SELECT
    ID,
    CASE WHEN top_priority = 1 THEN 1 ELSE 0 END AS Valid,
    CASE WHEN top_priority = 2 THEN 1 ELSE 0 END AS Expired,
    CASE WHEN top_priority = 3 THEN 1 ELSE 0 END AS NA,
    CASE WHEN top_priority = 4 THEN 1 ELSE 0 END AS WIP
FROM ranked_status;

这两种方案都避免了复杂的嵌套CASE逻辑，执行效率更高，同时代码可读性和可维护性也更强。

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