MySQL子查询统计范围未按订单限制及求和问题排查

咱们先梳理下你遇到的问题根源：你写的子查询没有和外层的订单ID做关联，所以不管是哪个订单，子查询都返回了全表的统计结果；而且你的查询是从customer_item左连customer_order，导致每个订单项都输出一行，没有按订单分组聚合。

接下来咱们一步步写出正确的统计SQL，满足你的需求：统计每个订单的有效行项数（quantity非空）、有效数量总和、已发货行项数、已发货数量总和，以及已发货数量占比。

正确的SQL查询语句 #

SELECT
    co.customer,
    co.id AS order_id,
    -- 统计有效行项数（quantity非空的条目数）
    COUNT(ci.quantity) AS total_items,
    -- 统计有效数量总和
    SUM(ci.quantity) AS total_quantity,
    -- 统计已发货的有效行项数（quantity非空且已发货）
    COUNT(CASE WHEN ci.shipment_date IS NOT NULL THEN 1 END) AS shipped_items,
    -- 统计已发货的数量总和
    SUM(CASE WHEN ci.shipment_date IS NOT NULL THEN ci.quantity ELSE 0 END) AS shipped_quantity,
    -- 计算已发货数量占比，处理除以0的情况（避免订单无有效数量时出错）
    CASE 
        WHEN SUM(ci.quantity) = 0 THEN 0 
        ELSE ROUND(SUM(CASE WHEN ci.shipment_date IS NOT NULL THEN ci.quantity ELSE 0 END) / SUM(ci.quantity), 2) 
    END AS shipped_ratio
FROM customer_order co
LEFT JOIN customer_item ci ON co.id = ci.order_id
GROUP BY co.id, co.customer
ORDER BY co.id;

代码解释 #

• 关联与分组：从customer_order出发左连customer_item，然后按订单ID和客户分组，确保每个订单只返回一行统计结果。
• COUNT(ci.quantity)：因为COUNT会自动忽略NULL值，所以直接统计quantity非空的行项数，比COUNT(*) WHERE quantity IS NOT NULL更简洁。
• 条件聚合：用CASE WHEN配合COUNT/SUM，只统计满足“已发货”条件的行项和数量。
• 占比计算：用CASE处理total_quantity为0的情况，避免出现除以0的错误，同时用ROUND保留两位小数让结果更直观。

执行结果 #

运行上面的SQL后，会得到符合你预期的结果：

+----------------+----------+-------------+----------------+---------------+----------------+--------------+
| customer       | order_id | total_items | total_quantity | shipped_items | shipped_quantity | shipped_ratio |
+----------------+----------+-------------+----------------+---------------+----------------+--------------+
| Spronketts LTD |        1 |           2 |              4 |             2 |              4 |         1.00 |
| Widgets Inc.   |        2 |           4 |             10 |             3 |              9 |         0.90 |
| Frobizz PLC    |        3 |           4 |              4 |             0 |              0 |         0.00 |
+----------------+----------+-------------+----------------+---------------+----------------+--------------+

（注：你的预期里订单1的total_items写的是4，但实际有效行项是2条（另外两条quantity为NULL），所以这里统计的是正确的有效行项数；如果你的需求是统计所有行项（包括quantity为NULL），可以调整为COUNT(ci.id)，但根据你的描述，应该是只统计quantity非空的有效行项）

如果你确实需要统计所有行项（包括quantity为NULL），再单独统计有效行项，可以修改对应的字段：

COUNT(ci.id) AS all_items, -- 所有行项数
COUNT(ci.quantity) AS valid_items, -- 有效行项数

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