如何使用ROW_NUMBER()按优先级匹配参数筛选最低价格？

示例数据表 #

id | doc_id | loc_id | price
---|--------|--------|------
1  | null   | 13     | 100
2  | 12     | 13     | 40
3  | 12     | null   | 300
4  | null   | null   | 150

筛选优先级规则 #

• 优先级1：优先选择doc_id和loc_id均匹配目标参数的行价格（示例参数doc_id=12、loc_id=13时，对应价格40）
• 优先级2：若优先级1无匹配，则选择仅doc_id匹配参数的行价格
• 优先级3：若前两级都无匹配，则选择仅loc_id匹配参数的行价格
• 优先级4：若以上都无匹配，则选择doc_id和loc_id均为null的行价格

基于ROW_NUMBER()的实现方案 #

利用ROW_NUMBER()函数结合自定义排序规则，给符合条件的行按优先级标记序号，最终取序号为1的行即可。假设目标参数为@p_doc_id=12、@p_loc_id=13，SQL代码如下：

WITH ranked_prices AS (
    SELECT 
        price,
        ROW_NUMBER() OVER (
            ORDER BY 
                -- 按优先级分配排序权重，数字越小优先级越高
                CASE 
                    WHEN doc_id = @p_doc_id AND loc_id = @p_loc_id THEN 1
                    WHEN doc_id = @p_doc_id AND loc_id IS NULL THEN 2
                    WHEN doc_id IS NULL AND loc_id = @p_loc_id THEN 3
                    WHEN doc_id IS NULL AND loc_id IS NULL THEN 4
                    ELSE 5 -- 其他不符合规则的行排最后，不会被选中
                END,
                price ASC -- 同优先级下筛选最低价格
        ) AS rn
    FROM 
        -- 实际场景替换为你的多表内连接语句
        your_table
    WHERE 
        -- 提前筛选出符合四类优先级规则的行，减少计算量
        (doc_id = @p_doc_id AND loc_id = @p_loc_id)
        OR (doc_id = @p_doc_id AND loc_id IS NULL)
        OR (doc_id IS NULL AND loc_id = @p_loc_id)
        OR (doc_id IS NULL AND loc_id IS NULL)
)
SELECT price
FROM ranked_prices
WHERE rn = 1;

多表连接场景适配 #

如果涉及多表内连接，只需将your_table替换为对应的连接逻辑即可，示例如下：

FROM 
    main_table
JOIN 
    related_table ON main_table.doc_id = related_table.doc_id
-- 按需添加其他表连接条件

逻辑说明 #

1. 通过CTEranked_prices给每一行标记优先级序号：CASE语句为不同匹配情况分配排序权重，确保高优先级的行序号更小
2. 同优先级下按price ASC排序，保证取到该优先级下的最低价格
3. 最后筛选rn=1的行，即为符合最高优先级要求的最低价格

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