SQL查询问题：按drug_id汇总total_amount失败的原因及解决方法

问题场景 #

原SQL用于计算每批药品的剩余库存total_amount，现在需要按drugs_shipment.drug_id对剩余库存进行汇总，但尝试的嵌套聚合+窗口函数的SQL无法正常运行。

错误查询的问题点 #

1. 窗口函数与聚合函数嵌套违反SQL执行顺序
   SQL的执行逻辑是：先完成FROM/JOIN、WHERE过滤，再执行GROUP BY和聚合函数，最后才处理窗口函数。你在外层SUM()里直接嵌套窗口函数SUM(...) OVER (...)，此时窗口函数还未执行，属于语法错误，数据库无法解析。

2. GROUP BY子句不完整
   你的SELECT中包含drug_name、drug_unit，但GROUP BY仅指定了drug_id。在开启严格SQL模式（如MySQL的ONLY_FULL_GROUP_BY）时，这会触发报错——非聚合列必须出现在GROUP BY中（或能被GROUP BY列唯一确定）。

3. 数据类型不匹配
   IFNULL(drugs_movement.amount, "0")中用字符串"0"填充NULL，但amount是数值类型，可能导致不必要的类型转换，建议改用数字0。

可行解决方案 #

方案一：先算单批库存，再汇总（直观易读） #

先通过子查询/CTE计算每一批的剩余库存，再按药品ID汇总：

WITH batch_remaining AS (
    SELECT 
        ds.drug_id,
        dd.name AS drug_name,
        ddu.name AS drug_unit,
        -- 计算单批剩余：初始量减去该批已出库量
        ds.initial_amount - COALESCE(SUM(dm.amount), 0) AS single_batch_stock
    FROM drugs_shipment ds
    JOIN drugs_drug dd ON ds.drug_id = dd.id
    JOIN drugs_drugunit ddu ON dd.unit_id = ddu.id
    LEFT JOIN drugs_movement dm 
        ON dm.shipment_id = ds.id 
        AND dm.date < '2025-12-11'
    WHERE ds.date_of_comming < '2025-12-11'
        AND (ds.date_of_run_out IS NULL OR ds.date_of_run_out > '2025-12-11')
    -- 按批次分组，确保每批计算一次剩余
    GROUP BY ds.id, ds.drug_id, dd.name, ddu.name, ds.initial_amount
)
-- 按药品ID汇总所有批次的剩余
SELECT 
    drug_id,
    drug_name,
    drug_unit,
    SUM(single_batch_stock) AS total_amount
FROM batch_remaining
GROUP BY drug_id, drug_name, drug_unit;

方案二：直接汇总计算（更高效） #

跳过单批计算，直接对符合条件的批次汇总总初始量，再减去总出库量：

SELECT 
    ds.drug_id,
    dd.name AS drug_name,
    ddu.name AS drug_unit,
    -- 总初始量 - 总出库量（无出库则减0）
    SUM(ds.initial_amount) - COALESCE(SUM(dm.amount), 0) AS total_amount
FROM drugs_shipment ds
JOIN drugs_drug dd ON ds.drug_id = dd.id
JOIN drugs_drugunit ddu ON dd.unit_id = ddu.id
LEFT JOIN drugs_movement dm 
    ON dm.shipment_id = ds.id 
    AND dm.date < '2025-12-11'
WHERE ds.date_of_comming < '2025-12-11'
    AND (ds.date_of_run_out IS NULL OR ds.date_of_run_out > '2025-12-11')
-- 按药品ID及关联名称分组，避免严格模式报错
GROUP BY ds.drug_id, dd.name, ddu.name;

补充说明 #

• 用COALESCE替代IFNULL：COALESCE是SQL标准函数，支持多参数，兼容性比IFNULL更好。
• GROUP BY中加入drug_name、drug_unit：因为同一drug_id对应的药品名称和单位是唯一的，加入后既符合SQL规范，也不会改变汇总结果。

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