PostgreSQL 14如何按指定warehouseId优先筛选产品表数据？

核心实现方案 #

使用窗口函数ROW_NUMBER()对同名称的产品分组排序，优先保留匹配指定warehouseId的记录，无匹配时取任意一条（示例中按id取第一条），具体SQL如下：

WITH ranked_products AS (
    SELECT 
        id,
        name,
        warehouseId,
        ROW_NUMBER() OVER (
            PARTITION BY name 
            ORDER BY CASE WHEN warehouseId = 2 THEN 0 ELSE 1 END, id
        ) AS rn
    FROM product
)
SELECT id, name, warehouseId
FROM ranked_products
WHERE rn = 1;

逻辑说明 #

1. 分组排序：通过PARTITION BY name将相同名称的产品归为一组，使用ORDER BY定义优先级：
   • 匹配指定warehouseId的记录标记为0，排在同组最前面
   • 不匹配的标记为1，排在后面
   • 追加id排序是为了在无匹配记录时，稳定取同组中id最小的记录（可根据需求替换为其他字段，比如warehouseId或随机排序）
2. 筛选结果：从临时结果集中筛选出每组编号为1的记录，即为最终需求的结果。

动态指定warehouseId #

如果需要动态传入warehouseId参数，可使用PostgreSQL的参数占位符：

WITH ranked_products AS (
    SELECT 
        id,
        name,
        warehouseId,
        ROW_NUMBER() OVER (
            PARTITION BY name 
            ORDER BY CASE WHEN warehouseId = $1 THEN 0 ELSE 1 END, id
        ) AS rn
    FROM product
)
SELECT id, name, warehouseId
FROM ranked_products
WHERE rn = 1;

执行时传入指定的warehouseId值即可（如2）。

示例验证 #

针对题目中的示例数据，执行上述SQL（指定warehouseId=2），将返回预期结果：

id  | name | warehouseId
-------------------------
 2  |  A   |    2
 3  |  B   |    1

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