多表SQL查询需求：显示全量库存商品的采购与交易月度数据

数据老陈

2026-8-6

问题解决

原查询存在几个问题，导致仅返回同时有采购和交易记录的商品：

后续WHERE条件过滤掉了无采购/交易的商品（这类记录的日期字段为NULL，不满足BETWEEN条件），抵消了FULL OUTER JOIN的作用
第二个FULL OUTER JOIN语法错误，缺少ON关键字
GROUP BY字段错误，应基于item_stock的字段分组，而非关联表字段

以下是修正后的SQL，可返回item_stock中所有商品，并统计2022年11月的采购量和交易量：

方法一：左连接+关联时过滤日期

SELECT 
    s.item_name,
    s.item_current_qty AS on_hand,
    COALESCE(SUM(p.item_purchase_qty), 0) AS purchased,
    COALESCE(SUM(t.item_txn_qty), 0) AS Txnd
FROM item_stock s
LEFT JOIN item_purchase p 
    ON p.item_id = s.item_id 
    AND p.item_purchase_date BETWEEN '2022-11-01' AND '2022-11-30'
LEFT JOIN item_transaction t 
    ON t.item_id = s.item_id 
    AND t.txn_date BETWEEN '2022-11-01' AND '2022-11-30'
GROUP BY s.item_id, s.item_name, s.item_current_qty
ORDER BY s.item_name ASC;

方法二：子查询预统计月度数据（性能更优）

先分别统计11月各商品的采购、交易总量，再与库存表左连接：

WITH monthly_purchase AS (
    SELECT 
        item_id,
        SUM(item_purchase_qty) AS purchased
    FROM item_purchase
    WHERE item_purchase_date BETWEEN '2022-11-01' AND '2022-11-30'
    GROUP BY item_id
),
monthly_transaction AS (
    SELECT 
        item_id,
        SUM(item_txn_qty) AS Txnd
    FROM item_transaction
    WHERE txn_date BETWEEN '2022-11-01' AND '2022-11-30'
    GROUP BY item_id
)
SELECT 
    s.item_name,
    s.item_current_qty AS on_hand,
    COALESCE(p.purchased, 0) AS purchased,
    COALESCE(t.Txnd, 0) AS Txnd
FROM item_stock s
LEFT JOIN monthly_purchase p ON p.item_id = s.item_id
LEFT JOIN monthly_transaction t ON t.item_id = s.item_id
ORDER BY s.item_name ASC;

说明：