SQL多表查询问题：显示所有商品及作废订单中的对应数量

原查询的问题 #

你的查询丢失部分商品的核心原因是WHERE子句的过滤逻辑破坏了LEFT JOIN保留所有Items行的特性：

• LEFT JOIN后，无对应交易记录或作废订单的商品行中，Sales Orders.Status为NULL，不满足"Sales Orders"."Status" = 'void'条件
• AND的优先级高于OR，实际执行逻辑为(Status='void' AND Quantity>=0) OR Quantity IS NULL，这会排除有交易记录但订单非作废状态的商品行，导致这类商品无法显示

修正后的查询方案 #

方案1：将订单状态过滤移至LEFT JOIN的ON子句

SELECT
    "Items"."Item ID",
    "Items"."Item Name",
    SUM(IF_NULL("Sales Order Items"."Quantity", 0)) AS void_order_quantity
FROM
    "Items"
LEFT JOIN "Sales Order Items" 
    ON "Items"."Item Name" = "Sales Order Items"."Item Name"
LEFT JOIN "Sales Orders" 
    ON "Sales Order Items"."Sales Order ID" = "Sales Orders"."Sales Order ID" 
    AND "Sales Orders"."Status" = 'void' -- 仅关联作废订单
WHERE "Sales Order Items"."Quantity" IS NULL OR "Sales Order Items"."Quantity" >= 0
GROUP BY "Items"."Item ID", "Items"."Item Name";

方案2：用CASE WHEN精准统计作废订单的数量

这种方式更直观，直接在SUM中判断订单状态是否为void，避免JOIN时的过滤问题：

SELECT
    "Items"."Item ID",
    "Items"."Item Name",
    SUM(
        CASE
            WHEN "Sales Orders"."Status" = 'void' AND "Sales Order Items"."Quantity" >= 0 THEN "Sales Order Items"."Quantity"
            ELSE 0
        END
    ) AS void_order_quantity
FROM
    "Items"
LEFT JOIN "Sales Order Items" 
    ON "Items"."Item Name" = "Sales Order Items"."Item Name"
LEFT JOIN "Sales Orders" 
    ON "Sales Order Items"."Sales Order ID" = "Sales Orders"."Sales Order ID" 
GROUP BY "Items"."Item ID", "Items"."Item Name";

针对最终目标的扩展（统计非作废订单数量） #

如果后续需要统计非作废订单的商品数量（用于库存计算），只需调整CASE WHEN的条件：

SELECT
    "Items"."Item ID",
    "Items"."Item Name",
    SUM(
        CASE
            WHEN "Sales Orders"."Status" != 'void' AND "Sales Order Items"."Quantity" >= 0 THEN "Sales Order Items"."Quantity"
            ELSE 0
        END
    ) AS valid_order_quantity
FROM
    "Items"
LEFT JOIN "Sales Order Items" 
    ON "Items"."Item Name" = "Sales Order Items"."Item Name"
LEFT JOIN "Sales Orders" 
    ON "Sales Order Items"."Sales Order ID" = "Sales Orders"."Sales Order ID" 
GROUP BY "Items"."Item ID", "Items"."Item Name";

关键说明 #

• 始终将LEFT JOIN的过滤条件放在ON子句而非WHERE中，才能保留左表（Items）的所有行
• 使用CASE WHEN可精准控制参与求和的行，避免NULL值或不符合条件的行干扰统计

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