如何编写同表SQL多级查询识别需移库的BULK库存

需求明确 #

从库存表iv_f中筛选出符合以下条件的BULK（不可拣选）库存记录：

• 同一SKU下，该BULK库存的收货日期dt_recv早于该SKU下任意一个非BULK（可拣选）库存的收货日期
• 每个BULK标签仅输出一次，避免重复

重复记录的核心原因 #

之前的SQL大概率是直接关联了BULK和非BULK记录，导致一个BULK标签对应多个非BULK记录时被重复输出；且未针对BULK标签做去重处理。

优化方案 #

方案1：子查询+关联去重 #

先算出每个SKU的非BULK库存最早收货日期，再关联筛选符合条件的BULK库存，最后用DISTINCT去重：

SELECT DISTINCT 
    iv.sku, iv.location, iv.bulk_tag, iv.dt_recv
FROM iv_f iv
JOIN (
    -- 子查询：获取每个SKU的非BULK库存最早收货日期
    SELECT 
        sku, 
        MIN(dt_recv) AS earliest_non_bulk_dt
    FROM iv_f
    WHERE location != 'BULK'
    GROUP BY sku
) non_bulk ON iv.sku = non_bulk.sku
WHERE iv.location = 'BULK'
  AND iv.dt_recv < non_bulk.earliest_non_bulk_dt;

• 子查询每个SKU仅返回一条记录，关联后不会产生重复关联导致的多条输出
• DISTINCT确保同一bulk_tag只出现一次

方案2：窗口函数简化逻辑 #

用窗口函数直接计算每个SKU的非BULK最早收货日期，筛选后去重：

SELECT DISTINCT
    sku, location, bulk_tag, dt_recv
FROM (
    SELECT 
        *,
        -- 计算当前SKU下所有非BULK库存的最早收货日期
        MIN(CASE WHEN location != 'BULK' THEN dt_recv END) OVER (PARTITION BY sku) AS earliest_non_bulk_dt
    FROM iv_f
) sub
WHERE location = 'BULK'
  AND dt_recv < earliest_non_bulk_dt;

• 窗口函数无需额外关联，逻辑更直观
• 子查询完成日期计算后，直接筛选BULK记录并去重

注意事项 #

• 确认bulk_tag是BULK库存的唯一标识，若不是，可结合sku、location、bulk_tag一起做去重
• 若某SKU没有非BULK库存，两个方案都会自动排除这类SKU（符合业务逻辑，无补货目标无需移库）

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