多表关联Pro_ID与Location_ID：整合多维度业务数据的SQL查询需求

核心需求 #

通过Pro_ID与Location_ID关联多表，整合输出位置名称、产品名称、采购总数量、调拨总数量、销售总数量。

现有基础查询（仅统计采购） #

已实现采购数据与位置、产品表的关联查询，SQL如下：

SELECT 
    location_tbl.location,
    product_tbl.pro_name,
    SUM(purchase_tbl.Qty) AS Total_Purchase 
FROM ((purchase_tbl 
    INNER JOIN location_tbl ON purchase_tbl.Location_ID = location_tbl.Location_ID)
    INNER JOIN product_tbl ON purchase_tbl.item_ID = product_tbl.Pro_ID)
GROUP BY location_tbl.location, product_tbl.pro_name;

完整关联整合方案 #

要加入调拨、销售数据，需关联对应业务数据表（假设调拨表为transfer_tbl、销售表为sales_tbl，两表均包含Location_ID、Pro_ID及对应数量字段）。使用LEFT JOIN避免数据遗漏，同时用COALESCE处理空值，确保结果格式统一：

SELECT 
    loc.location,
    prod.pro_name,
    COALESCE(SUM(pur.Qty), 0) AS Total_Purchase,
    COALESCE(SUM(tfr.tfr_qty), 0) AS Total_Transfer,
    COALESCE(SUM(sls.sales_qty), 0) AS Total_Sales
FROM product_tbl prod
CROSS JOIN location_tbl loc
LEFT JOIN purchase_tbl pur 
    ON pur.item_ID = prod.Pro_ID 
    AND pur.Location_ID = loc.Location_ID
LEFT JOIN transfer_tbl tfr 
    ON tfr.Pro_ID = prod.Pro_ID 
    AND tfr.Location_ID = loc.Location_ID
LEFT JOIN sales_tbl sls 
    ON sls.Pro_ID = prod.Pro_ID 
    AND sls.Location_ID = loc.Location_ID
GROUP BY loc.location, prod.pro_name
ORDER BY loc.location, prod.pro_name;

关键说明 #

• 用CROSS JOIN生成所有位置-产品组合，确保即使某位置无某产品的交易记录，也能显示该组合（对应数量为0）
• LEFT JOIN关联各业务表，避免因某表无对应记录而过滤掉有效数据
• COALESCE将SUM返回的NULL替换为0，避免结果出现空值
• 所有关联逻辑统一基于Pro_ID（产品表与业务表的产品标识）和Location_ID（位置表与业务表的位置标识）

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