MySQL中聚合重量列计算的子查询性能优化及无自查询实现方案咨询

你的核心问题是原查询里的子查询是按整个订单（orderno）计算总重量，但你改写后用的SUM(a.qty_each * c.grossWeight)因为外层GROUP BY a.sku, a.organizationId, a.warehouseId，变成了按每个sku分组计算，所以结果和预期不符。下面提供两种无需子查询的优化方案，既能保证逻辑正确，又能提升性能：

方案1：使用窗口函数计算订单级总和 #

窗口函数可以在分组的同时，计算整个订单的聚合值，避免子查询的重复执行。适合支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server等）：

SELECT ...., 
       CASE 
           WHEN MIN(IFNULL(g.grossWeight, 0)) = 0 THEN 
               CONCAT('( ', TRIM(SUM(a.qty_each * c.grossWeight) OVER (PARTITION BY a.orderno)) + 0, 'g )')
           ELSE 
               CONCAT('( ', ROUND(g.grossWeight), 'g )') 
       END AS WEIGHT, 
       ... 
FROM table a 
LEFT JOIN table b ON a.organizationId = b.organizationId AND a.warehouseId = b.warehouseId AND a.orderNo = b.orderNo 
LEFT JOIN table_sku c ON a.organizationId = c.organizationId AND a.sku = c.sku AND a.customerId = c.customerId 
LEFT JOIN DOC_ORDER_PACKING_SUMMARY g ON a.organizationId = g.organizationId AND a.warehouseId = g.warehouseId AND a.orderNo = g.orderNo AND a.pickToTraceId = g.traceId 
WHERE ... 
GROUP BY a.sku, a.organizationId, a.warehouseId, a.orderno -- 根据数据库SQL模式调整，确保非聚合字段都在GROUP BY中

逻辑说明： #

SUM(a.qty_each * c.grossWeight) OVER (PARTITION BY a.orderno)会忽略外层的sku分组，单独按orderno计算整个订单的总重量，和原查询子查询的逻辑完全一致，但窗口函数的执行效率远高于子查询（尤其是数据量较大时）。

方案2：提前预聚合订单级重量（CTE/临时表） #

如果你的数据库不支持窗口函数（比如MySQL 5.x），可以用CTE或临时表先计算所有订单的总重量，再关联到主查询中：

-- 预聚合每个订单的总重量
WITH order_total_weight AS (
    SELECT 
        aad.orderno,
        TRIM(SUM(aad.qty_each * bs.grossWeight)) + 0 AS total_weight
    FROM act_allocation_details aad 
    LEFT JOIN BAS_SKU bs 
        ON aad.organizationId = bs.organizationId 
        AND aad.sku = bs.sku 
        AND aad.customerId = bs.customerId 
    GROUP BY aad.orderno
)

SELECT ...., 
       CASE 
           WHEN MIN(IFNULL(g.grossWeight, 0)) = 0 THEN 
               CONCAT('( ', otw.total_weight, 'g )')
           ELSE 
               CONCAT('( ', ROUND(g.grossWeight), 'g )') 
       END AS WEIGHT, 
       ... 
FROM table a 
LEFT JOIN table b ON a.organizationId = b.organizationId AND a.warehouseId = b.warehouseId AND a.orderNo = b.orderNo 
LEFT JOIN table_sku c ON a.organizationId = c.organizationId AND a.sku = c.sku AND a.customerId = c.customerId 
LEFT JOIN DOC_ORDER_PACKING_SUMMARY g ON a.organizationId = g.organizationId AND a.warehouseId = g.warehouseId AND a.orderNo = g.orderNo AND a.pickToTraceId = g.traceId 
LEFT JOIN order_total_weight otw ON a.orderno = otw.orderno -- 关联预聚合结果
WHERE ... 
GROUP BY a.sku, a.organizationId, a.warehouseId, otw.total_weight -- 根据SQL模式调整GROUP BY字段

逻辑说明： #

CTEorder_total_weight只计算一次所有订单的总重量，主查询直接关联获取值，避免了原查询中子查询对每一行重复计算的性能损耗，数据量越大，性能提升越明显。

额外性能优化建议 #

• 给act_allocation_details(orderno, organizationId, sku, customerId)和BAS_SKU(organizationId, sku, customerId)建立联合索引，加速预聚合或窗口函数的关联计算。
• 如果使用MySQL，确保关闭ONLY_FULL_GROUP_BY或者将所有SELECT中的非聚合字段加入GROUP BY，避免语法错误。

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