如何实现含Withdrawn/Not Available逻辑的规则优先级行筛选？

业务规则回顾 #

• 按Class分区，规则优先级：CSXBusrule > CSX > CSXFW
• 若某规则的最高版本为Withdrawn，直接排除该规则，取下一优先级规则
• 忽略RuleAvailable为Not Available的版本，取同规则的下一个版本
• 每个Class仅返回1条符合要求的记录

修正思路 #

分步骤处理：先过滤无效版本，再排查每个规则的最高版本是否为Withdrawn，最后按优先级筛选每个Class的目标记录。

完整SQL代码 #

WITH filtered_versions AS (
    -- 第一步：过滤Not Available版本，给规则分配优先级权重（数字越小优先级越高）
    SELECT
        Class,
        Key,
        rule,
        version,
        CASE rule
            WHEN 'CSXBusrule' THEN 1
            WHEN 'CSX' THEN 2
            WHEN 'CSXFW' THEN 3
            ELSE 4 -- 其他规则优先级最低
        END AS rule_priority
    FROM your_table_name
    WHERE RuleAvailable != 'Not Available'
),
rule_latest_version AS (
    -- 第二步：获取每个(Class, rule)的最高版本，并标记是否为Withdrawn
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY Class, rule ORDER BY version DESC) AS version_rank,
        CASE WHEN version = 'Withdrawn' THEN 1 ELSE 0 END AS is_withdrawn
    FROM filtered_versions
),
valid_rule_set AS (
    -- 第三步：排除最高版本为Withdrawn的规则，保留有效规则
    SELECT
        Class,
        Key,
        rule,
        version,
        rule_priority
    FROM rule_latest_version
    WHERE version_rank = 1
      AND is_withdrawn = 0
),
final_result AS (
    -- 第四步：每个Class取优先级最高的有效规则
    SELECT
        Class,
        Key,
        rule,
        version
    FROM (
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY Class ORDER BY rule_priority ASC) AS rule_rank
        FROM valid_rule_set
    ) t
    WHERE rule_rank = 1
)
SELECT * FROM final_result;

关键细节说明 #

1. 版本排序：如果version是语义化版本（如v2.1.0），需替换ORDER BY version DESC为对应数据库的版本排序函数，比如PostgreSQL用ORDER BY version(version) DESC，MySQL可自定义排序逻辑。
2. 规则优先级：如果有其他规则类型，可在CASE rule分支中补充对应的权重值。
3. 空值处理：若RuleAvailable可能为NULL，需调整WHERE条件为RuleAvailable IS NOT NULL AND RuleAvailable != 'Not Available'。

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