PostgreSQL条件递归SQL查询：查找缺货商品的替代零售商

思路说明 #

要解决这个问题，核心是为t1中每个缺货商品匹配同公司内其他有货的零售商，且在多个可选零售商中筛选出库存数量最多的。我们可以通过窗口函数对可选零售商按库存数量排序，再取每个缺货商品对应的最优选项；同时通过左连接处理无替代选项的场景。

修正后的表结构（补充quantity字段） #

你提供的t2脚本缺少需求中提到的quantity字段，先补充完整：

CREATE TABLE t2 
(
    company VARCHAR(512),
    retailer VARCHAR(512),
    product VARCHAR(512),
    availability VARCHAR(512),
    quantity INT -- 补充库存数字段
);

最终SQL查询 #

WITH possible_replacements AS (
    SELECT
        t1.company,
        t1.retailer AS original_retailer,
        t1.product,
        t1.availability AS original_availability,
        t2.retailer AS corresponding_retailer,
        t2.availability AS product_availability,
        t2.quantity,
        -- 按库存降序排序，同商品同公司下库存最高的排第1
        ROW_NUMBER() OVER (
            PARTITION BY t1.company, t1.retailer, t1.product
            ORDER BY t2.quantity DESC
        ) AS rn
    FROM t1
    LEFT JOIN t2 
        ON t1.company = t2.company
        AND t1.product = t2.product
        AND t1.retailer != t2.retailer -- 排除自身零售商
        AND t2.availability = '1' -- 只选有货的
    WHERE t1.availability = '0' -- 只处理t1中的缺货商品
)
SELECT
    company,
    original_retailer AS retailer,
    product,
    original_availability AS availability,
    COALESCE(corresponding_retailer, 'NA') AS corresponding_retailer,
    COALESCE(product_availability, 'NA') AS product_availability
FROM possible_replacements
WHERE rn = 1 OR rn IS NULL -- 取最优选项，或无替代的记录
ORDER BY company, original_retailer, product;

逻辑解释 #

1. CTE possible_replacements：
   • 关联t1和t2，匹配同公司、同商品、不同零售商且有货的记录
   • 用ROW_NUMBER()窗口函数，按company+retailer+product分组，每组内按quantity降序排序，标记序号rn
2. 主查询：
   • 筛选rn=1的记录（即每组中库存最高的替代零售商），同时保留rn IS NULL的记录（无替代选项的缺货商品）
   • 用COALESCE将空值替换为NA，符合预期输出格式

适配示例数据（无quantity字段的情况） #

如果实际场景中t2暂时没有quantity字段，仅需按任意优先级选择一个有货零售商，可以将排序条件改为ORDER BY t2.retailer（或其他字段）：

ROW_NUMBER() OVER (
    PARTITION BY t1.company, t1.retailer, t1.product
    ORDER BY t2.retailer -- 按零售商名称排序取第一个
) AS rn

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