MySQL按工作中心实现带重置条件的累计工时分配问题

需求回顾 #

• 按Workcenter分组，累计TableOrders中的Total Execution Time
• 累计值达到TableHours当日可用工时阈值时，自动切换到下一个非0可用日期
• 若单个订单工时超过当日剩余容量，直接顺延至能容纳该订单的最早日期
• 最终输出每个订单的分配日期结果

核心实现思路 #

1. 给每个工作中心的订单按业务规则排序，计算累计执行工时，明确每个订单的工时区间
2. 对每个工作中心的可用日期，计算累计可用工时，形成连续的容量区间
3. 关联订单工时区间和日期容量区间，匹配对应的分配日期，单独处理单个订单超单日容量的特殊情况

表结构参考 #

假设两张表的基础结构如下：

• TableHours：Workcenter（工作中心ID）、WorkDate（可用日期）、AvailableHours（当日可用工时，仅取>0的记录）
• TableOrders：Workcenter（工作中心ID）、OrderID（订单ID）、TotalExecutionTime（订单总执行工时）

具体SQL实现 #

WITH OrderedOrders AS (
    -- 给订单排序并计算累计工时
    SELECT
        Workcenter,
        OrderID,
        TotalExecutionTime,
        -- 累计到当前订单的总工时
        SUM(TotalExecutionTime) OVER (
            PARTITION BY Workcenter
            ORDER BY OrderID -- 替换为实际业务的订单优先级字段，比如创建时间、优先级
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS CumulativeExecutionTime,
        -- 当前订单的起始工时（上一个订单的累计值）
        COALESCE(SUM(TotalExecutionTime) OVER (
            PARTITION BY Workcenter
            ORDER BY OrderID
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ), 0) AS PreviousCumulativeTime
    FROM TableOrders
),
AvailableCapacity AS (
    -- 计算可用日期的累计容量区间
    SELECT
        Workcenter,
        WorkDate,
        AvailableHours,
        -- 累计到当前日期的总可用工时
        SUM(AvailableHours) OVER (
            PARTITION BY Workcenter
            ORDER BY WorkDate
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS CumulativeAvailableHours,
        -- 当前日期的起始容量（上一个日期的累计值）
        COALESCE(SUM(AvailableHours) OVER (
            PARTITION BY Workcenter
            ORDER BY WorkDate
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ), 0) AS PreviousCumulativeCapacity
    FROM TableHours
    WHERE AvailableHours > 0 -- 过滤无效的0工时日期
)
-- 关联匹配分配日期，处理超容量场景
SELECT
    oo.Workcenter,
    oo.OrderID,
    oo.TotalExecutionTime,
    CASE
        -- 单个订单工时超过单日最大可用工时，找第一个能完整容纳的日期
        WHEN oo.TotalExecutionTime > (SELECT MAX(AvailableHours) FROM AvailableCapacity ac_max WHERE ac_max.Workcenter = oo.Workcenter) THEN
            (SELECT MIN(WorkDate)
             FROM AvailableCapacity ac_sub
             WHERE ac_sub.Workcenter = oo.Workcenter
               AND ac_sub.CumulativeAvailableHours >= oo.PreviousCumulativeTime + oo.TotalExecutionTime)
        -- 正常匹配：订单工时落在当前日期的容量区间内
        ELSE ac.WorkDate
    END AS AssignedDate
FROM OrderedOrders oo
LEFT JOIN AvailableCapacity ac
    ON oo.Workcenter = ac.Workcenter
    AND oo.PreviousCumulativeTime < ac.CumulativeAvailableHours
    AND oo.CumulativeExecutionTime <= ac.CumulativeAvailableHours
ORDER BY oo.Workcenter, oo.OrderID;

关键注意事项 #

• 订单排序逻辑：ORDER BY OrderID必须替换为业务实际的订单优先级规则（比如CreateTime、Priority），否则分配顺序会出错
• 超容量处理：子查询会自动找到能完整容纳大订单的最早日期，避免拆分订单（若需要拆分订单，需额外调整逻辑）
• 边界情况：如果可用工时总容量小于订单总工时，超出部分的AssignedDate会返回NULL，可根据业务需求添加默认值或标记

内容的提问来源于stack exchange，提问作者Sven Schöninger