如何编写SQL查询实现全量ITEM-LOCATION组合的左连接结果

现有表结构与数据 #

表1（审批状态表） #

H_ID   STATUS
1001   Approved
1002   Approved
1003   Approved

表2（库存记录表） #

H_ID   L_NO    ITEM_ID   LOCATION   QTY
1001    1      220050    S1         5
1002    1      220050    S2         1
1002    2      230050    S2         4
1003    1      220050    S3         3
1003    2      230050    S3         2
1003    3      240050    S3         5

需求目标 #

生成所有ITEM_ID与所有LOCATION的组合，对应有库存记录的显示实际QTY，无记录的显示0，输出格式如下：

ITEM_ID       LOCATION  QTY
220050         S1        5
220050         S2        1
220050         S3        3
230050         S1        0
230050         S2        4
230050         S3        2
240050         S1        0
240050         S2        0
240050         S3        5

实现SQL语句 #

基础版本（假设表2所有H_ID均已在表1审批通过） #

WITH all_combinations AS (
    -- 生成所有ITEM_ID与LOCATION的全量组合
    SELECT DISTINCT t2.ITEM_ID, loc.LOCATION
    FROM 表2 t2
    CROSS JOIN (SELECT DISTINCT LOCATION FROM 表2) loc
),
item_location_qty AS (
    -- 统计每个ITEM_ID在对应LOCATION下的总数量
    SELECT ITEM_ID, LOCATION, SUM(QTY) AS QTY
    FROM 表2
    GROUP BY ITEM_ID, LOCATION
)
-- 左连接组合表与统计结果，空值替换为0
SELECT 
    ac.ITEM_ID,
    ac.LOCATION,
    COALESCE(ilq.QTY, 0) AS QTY
FROM all_combinations ac
LEFT JOIN item_location_qty ilq 
    ON ac.ITEM_ID = ilq.ITEM_ID 
    AND ac.LOCATION = ilq.LOCATION
ORDER BY ac.ITEM_ID, ac.LOCATION;

严谨版本（关联表1过滤已审批的H_ID） #

如果表2中存在未在表1审批通过的H_ID，需要加入表1的过滤条件：

WITH all_combinations AS (
    SELECT DISTINCT t2.ITEM_ID, loc.LOCATION
    FROM 表2 t2
    -- 仅保留表1中状态为Approved的H_ID对应的记录
    JOIN 表1 t1 ON t2.H_ID = t1.H_ID AND t1.STATUS = 'Approved'
    CROSS JOIN (
        SELECT DISTINCT LOCATION 
        FROM 表2 t2
        JOIN 表1 t1 ON t2.H_ID = t1.H_ID AND t1.STATUS = 'Approved'
    ) loc
),
item_location_qty AS (
    SELECT t2.ITEM_ID, t2.LOCATION, SUM(t2.QTY) AS QTY
    FROM 表2 t2
    JOIN 表1 t1 ON t2.H_ID = t1.H_ID AND t1.STATUS = 'Approved'
    GROUP BY t2.ITEM_ID, t2.LOCATION
)
SELECT 
    ac.ITEM_ID,
    ac.LOCATION,
    COALESCE(ilq.QTY, 0) AS QTY
FROM all_combinations ac
LEFT JOIN item_location_qty ilq 
    ON ac.ITEM_ID = ilq.ITEM_ID 
    AND ac.LOCATION = ilq.LOCATION
ORDER BY ac.ITEM_ID, ac.LOCATION;

语句说明 #

1. all_combinations CTE：使用CROSS JOIN生成所有ITEM_ID和LOCATION的笛卡尔积，确保每个商品都能匹配到所有仓库位置。
2. item_location_qty CTE：按ITEM_ID和LOCATION分组统计实际库存数量。
3. LEFT JOIN + COALESCE：将全量组合与实际统计结果左连接，用COALESCE把无记录的NULL值替换为0，最后按商品和位置排序得到目标输出。

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