如何基于聚合列值筛选对应列值？仓库库存SQL查询优化

原查询的核心问题 #

• GROUP BY包含itemid和自定义CASE表达式，导致同一仓库（WHS_ID）返回多行记录
• 子查询的关联逻辑错误：MAX(WORK.Record_Date) = MAX(LWD.LAST_WORK_DATE)无法正确绑定到当前仓库的最新异动记录
• 未正确关联WHSLoc、InventSum、Work三张表，导致异动日期和物料ID的匹配完全错误

可行解决方案 #

以下两种方案均能实现每个仓库一行记录，无库存时返回最新异动日期对应的物料ID的需求，需根据实际表结构补充关联条件：

方案1：先聚合最大异动日期，再关联取对应物料 #

WITH WarehouseLatest AS (
    SELECT
        WL.WHS_ID,
        MAX(w.Record_Date) AS last_work_date
    FROM DB.WHSLoc WL
    -- 补充WHSLoc与InventSum的关联条件，例：WL.WHS_ID = INV.WHS_ID
    LEFT JOIN DB.InventSum INV ON WL.WHS_ID = INV.WHS_ID
    -- 补充Work与InventSum的关联条件，例：INV.ItemID = w.ItemID AND INV.WHS_ID = w.WHS_ID
    LEFT JOIN DB.Work w ON INV.ItemID = w.ItemID AND INV.WHS_ID = w.WHS_ID
    WHERE INV.PHYSICALINVENT IS NULL -- 筛选无库存仓库
    GROUP BY WL.WHS_ID
)
SELECT
    WL.WHS_ID AS WHS_Loc,
    -- 有库存时显示物料ID，无库存时显示NULL
    CASE WHEN INV.PHYSICALINVENT IS NOT NULL THEN INV.ItemID ELSE NULL END AS ItemID,
    INV.PHYSICALINVENT AS Phys_Inv,
    wl.last_work_date AS Last_Work,
    -- 拼接后缀（如-00001）可按需添加
    CONCAT(w.ItemID, '-00001') AS Last_Item
FROM DB.WHSLoc WL
LEFT JOIN DB.InventSum INV ON WL.WHS_ID = INV.WHS_ID
LEFT JOIN WarehouseLatest wl ON WL.WHS_ID = wl.WHS_ID
-- 关联回Work表，获取对应最新日期的物料ID
LEFT JOIN DB.Work w ON w.Record_Date = wl.last_work_date AND INV.WHS_ID = w.WHS_ID
GROUP BY WL.WHS_ID, INV.PHYSICALINVENT, INV.ItemID, wl.last_work_date, w.ItemID
ORDER BY WL.WHS_ID;

方案2：使用窗口函数筛选最新记录 #

WITH WarehouseRecords AS (
    SELECT
        WL.WHS_ID AS WHS_Loc,
        CASE WHEN INV.PHYSICALINVENT IS NOT NULL THEN INV.ItemID ELSE NULL END AS ItemID,
        INV.PHYSICALINVENT AS Phys_Inv,
        w.Record_Date AS Last_Work,
        CONCAT(w.ItemID, '-00001') AS Last_Item,
        -- 按仓库分组，异动日期倒序排序，标记最新记录
        ROW_NUMBER() OVER (PARTITION BY WL.WHS_ID ORDER BY w.Record_Date DESC) AS row_num
    FROM DB.WHSLoc WL
    LEFT JOIN DB.InventSum INV ON WL.WHS_ID = INV.WHS_ID
    LEFT JOIN DB.Work w ON INV.ItemID = w.ItemID AND INV.WHS_ID = w.WHS_ID
)
SELECT WHS_Loc, ItemID, Phys_Inv, Last_Work, Last_Item
FROM WarehouseRecords
WHERE row_num = 1 -- 仅保留每个仓库的最新记录
ORDER BY WHS_Loc;

关键注意事项 #

1. 必须补充三张表之间的关联条件（原查询完全缺失），否则会出现笛卡尔积，导致结果混乱
2. 若无需固定后缀，直接用w.ItemID替代CONCAT(w.ItemID, '-00001')即可
3. 有库存的仓库会自动返回Last_Work和Last_Item为NULL，符合需求

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