求修改MySQL语句：按从大到小顺序计算总量可填充不同容量的次数

问题说明 #

需要编写MySQL语句，计算总量grand_total_s按最大容量volume优先的顺序，依次用剩余量判断后续较小容量可填充的次数times_filled。当前语句仅计算每个容量单独可填充的次数，没有按从大到小的顺序分配剩余量，导致结果不符合预期。

期望结果 #

当前错误结果 #

解决方案 #

核心思路是先计算每个menu_item_id和school_id对应的总需求量grand_total_s，再按容量从大到小排序，逐步计算剩余量并确定每个容量的填充次数。以下是修正后的SQL语句：

WITH base_data AS (
    SELECT
        fm.menu_item_id,
        sa.school_id,
        mi.product_name,
        SUM(fm.volume_per_s) AS total_s,
        SUM(fm.volume_per_s) * sa.enrollment AS grand_total_s,
        miv.volume,
        sa.sp_id
    FROM feeding_menu fm
    INNER JOIN menu_items mi ON fm.menu_item_id = mi.id
    CROSS JOIN sp_allocated_schools sa
    LEFT JOIN menu_item_volumes miv ON mi.id = miv.menu_item_id
    WHERE sa.`status` = 1
    GROUP BY fm.menu_item_id, sa.school_id, mi.product_name, miv.volume, sa.sp_id
),
ranked_volumes AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY menu_item_id, school_id ORDER BY volume DESC) AS rn
    FROM base_data
),
allocation_calc AS (
    SELECT
        *,
        -- 计算当前步骤的剩余量：初始为总量，后续为上一步剩余量
        @remaining := CASE
            WHEN rn = 1 THEN grand_total_s
            ELSE @remaining - (FLOOR(@prev_remaining / volume) * volume)
        END AS current_remaining,
        -- 记录上一步剩余量，供下一行计算使用
        @prev_remaining := @remaining AS prev_remaining
    FROM ranked_volumes
    CROSS JOIN (SELECT @remaining := 0, @prev_remaining := 0) AS vars
    ORDER BY menu_item_id, school_id, rn
)
SELECT
    menu_item_id,
    school_id,
    grand_total_s,
    volume,
    -- 根据剩余量计算当前容量的填充次数
    CASE
        WHEN rn = 1 THEN FLOOR(grand_total_s / volume)
        ELSE FLOOR(current_remaining / volume)
    END AS times_filled
FROM allocation_calc
ORDER BY product_name ASC, volume DESC;

关键逻辑说明 #

1. base_data CTE：先聚合基础数据，算出每个分组的总需求量grand_total_s以及对应的所有容量选项。
2. ranked_volumes CTE：对每个menu_item_id和school_id下的容量按降序排序，标记处理顺序编号rn。
3. allocation_calc CTE：使用变量追踪剩余量，从最大容量开始计算填充次数，每一步更新剩余量供后续小容量使用。
4. 最终查询：根据剩余量计算每个容量的填充次数，确保按大到小的优先级分配总量。

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