如何在commission_rates表中优先返回自定义佣金率行

问题背景 #

我有一张commission_rates表，数据如下：

表的设计规则：

• publisher_id为NULL时，是通用费率，适用于所有发布商
• publisher_id非NULL时，是针对该发布商的自定义费率，优先级高于同商家的通用费率

需求：查询指定发布商的适用佣金率时，若存在该发布商的自定义费率，返回自定义行并替换同商家的通用费率；若无自定义费率，则返回所有通用费率行。

示例：

• 查询发布商408，返回行1、2、3、4（用自定义行4替换商家3的通用标准费率行5）
• 查询其他发布商（如10），返回行1、2、3、5

解决方案 #

通过窗口函数实现优先级筛选，SQL语句如下：

SELECT id, kind, rate, merchant_id, publisher_id
FROM (
    SELECT 
        *,
        -- 按商家+费率类型分组，自定义费率优先级高于通用费率
        ROW_NUMBER() OVER (
            PARTITION BY merchant_id, kind 
            ORDER BY CASE WHEN publisher_id = 408 THEN 0 ELSE 1 END
        ) AS rn
    FROM commission_rates
    -- 筛选目标发布商的自定义费率，或所有通用费率
    WHERE publisher_id = 408 OR publisher_id IS NULL
) t
-- 保留每组中优先级最高的行
WHERE rn = 1;

逻辑说明 #

1. 内层查询：
   • 先筛选出符合条件的行：要么是目标发布商的自定义费率，要么是通用费率
   • 用ROW_NUMBER()按merchant_id和kind分组排序：目标发布商的自定义费率排第0位，通用费率排第1位，确保每组内优先级最高的行rn=1
2. 外层查询：只取每组中rn=1的行，即每个商家+费率类型下的最优费率

如果需要动态传入发布商ID，可将408替换为参数（以PostgreSQL为例）：

SELECT id, kind, rate, merchant_id, publisher_id
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY merchant_id, kind 
            ORDER BY CASE WHEN publisher_id = $1 THEN 0 ELSE 1 END
        ) AS rn
    FROM commission_rates
    WHERE publisher_id = $1 OR publisher_id IS NULL
) t
WHERE rn = 1;

验证结果 #

• 传入发布商ID=408时，返回结果：

• 传入其他发布商ID（如10）时，返回结果：

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