Oracle整箱零箱统计场景下如何获取SUM求和的正确结果

问题错误原因 #

• 你使用逗号连接主表与无关联条件的子查询，生成了笛卡尔积：子查询计算的是全表汇总的FULLBOX和SPARE_BOX，导致主表每一行数据都拼接了全局汇总的错误值
• 计算逻辑不符合需求：FULLBOX和SPARE_BOX是每行商品独立计算的指标，不需要提前在子查询做SUM聚合，也不需要用ROUND函数处理
• 多余的分组聚合：你期望的结果是每行商品对应自身的箱数统计，直接逐行计算即可，不需要加GROUP BY和SUM函数

修正后的SQL（适配多数数据库，可根据自身使用的数据库调整整除、取余函数） #

SELECT 
    itm_code AS itmcd,
    itm_name AS itmname,
    PACKING_STYLE,
    TOTAL_QUANTITY,
    FULLBOX,
    SPARE_BOX,
    FULLBOX + SPARE_BOX AS TOTALBOX
FROM (
    SELECT 
        itm_code,
        itm_name,
        PACKING_STYLE,
        TOTAL_QUANTITY,
        -- 取整除的整数部分，MySQL可直接写 TOTAL_QUANTITY DIV PACKING_STYLE，Oracle可用TRUNC函数
        FLOOR(TOTAL_QUANTITY / PACKING_STYLE) AS FULLBOX,
        -- 取余判断是否需要备用箱，Oracle可替换为 MOD(TOTAL_QUANTITY, PACKING_STYLE)
        CASE WHEN TOTAL_QUANTITY % PACKING_STYLE = 0 THEN 0 ELSE 1 END AS SPARE_BOX
    FROM log0048d
    WHERE reqst_no = 'SMO21071900398'
) t

如果不需要嵌套子查询，也可以直接把计算逻辑写在最外层，语句更简洁：

SELECT 
    itm_code AS itmcd,
    itm_name AS itmname,
    PACKING_STYLE,
    TOTAL_QUANTITY,
    FLOOR(TOTAL_QUANTITY / PACKING_STYLE) AS FULLBOX,
    CASE WHEN TOTAL_QUANTITY % PACKING_STYLE = 0 THEN 0 ELSE 1 END AS SPARE_BOX,
    FLOOR(TOTAL_QUANTITY / PACKING_STYLE) + CASE WHEN TOTAL_QUANTITY % PACKING_STYLE = 0 THEN 0 ELSE 1 END AS TOTALBOX
FROM log0048d
WHERE reqst_no = 'SMO21071900398'

内容的提问来源于stack exchange，提问作者Khanh Van Luong