如何将有效工时分配至排班时段？SQL逻辑实现求助

以下SQL逻辑可以实现将表A的工时分配到表B的排班时段，生成预期结果：

WITH shift_blocks AS (
    -- 合并表B的上下午班次为统一时段
    SELECT 
        name,
        STR_TO_DATE(AM_start_time, '%H:%i') AS start_time,
        STR_TO_DATE(AM_end_time, '%H:%i') AS end_time,
        TIMESTAMPDIFF(HOUR, STR_TO_DATE(AM_start_time, '%H:%i'), STR_TO_DATE(AM_end_time, '%H:%i')) AS block_hours
    FROM table_b
    UNION ALL
    SELECT 
        name,
        STR_TO_DATE(PM_start_time, '%H:%i') AS start_time,
        STR_TO_DATE(PM_end_time, '%H:%i') AS end_time,
        TIMESTAMPDIFF(HOUR, STR_TO_DATE(PM_start_time, '%H:%i'), STR_TO_DATE(PM_end_time, '%H:%i')) AS block_hours
    FROM table_b
),
total_available AS (
    -- 计算每个用户当天总可用工时
    SELECT name, SUM(block_hours) AS total_hours
    FROM shift_blocks
    GROUP BY name
),
project_cumulative AS (
    -- 按用户分组，计算项目工时的累计值（按项目编号排序）
    SELECT 
        name,
        project_number,
        hours_qty,
        SUM(hours_qty) OVER (PARTITION BY name ORDER BY project_number) AS cumulative_hours
    FROM table_a
),
shift_cumulative AS (
    -- 按用户分组，计算时段的累计时长（按时间顺序排序）
    SELECT 
        sb.name,
        sb.start_time,
        sb.end_time,
        sb.block_hours,
        SUM(sb.block_hours) OVER (PARTITION BY sb.name ORDER BY sb.start_time) AS cumulative_block_hours,
        SUM(sb.block_hours) OVER (PARTITION BY sb.name ORDER BY sb.start_time) - sb.block_hours AS prev_cumulative
    FROM shift_blocks sb
    ORDER BY sb.name, sb.start_time
),
project_allocations AS (
    -- 将项目工时分配到对应时段
    SELECT 
        sc.name,
        pc.project_number,
        -- 计算项目在当前时段的起始时间
        CASE 
            WHEN pc.cumulative_hours - pc.hours_qty > sc.prev_cumulative THEN 
                DATE_ADD(sc.start_time, INTERVAL (pc.cumulative_hours - pc.hours_qty - sc.prev_cumulative) HOUR)
            ELSE sc.start_time
        END AS start_time,
        -- 计算项目在当前时段的结束时间
        CASE 
            WHEN pc.cumulative_hours < sc.cumulative_block_hours THEN 
                DATE_ADD(sc.start_time, INTERVAL (pc.cumulative_hours - sc.prev_cumulative) HOUR)
            ELSE sc.end_time
        END AS end_time,
        -- 计算当前时段分配的工时数
        LEAST(pc.cumulative_hours, sc.cumulative_block_hours) - GREATEST(pc.cumulative_hours - pc.hours_qty, sc.prev_cumulative) AS allocated_hours
    FROM shift_cumulative sc
    JOIN project_cumulative pc ON sc.name = pc.name
    WHERE pc.cumulative_hours > sc.prev_cumulative 
      AND pc.cumulative_hours - pc.hours_qty < sc.cumulative_block_hours
),
used_hours AS (
    -- 统计每个用户已分配的项目总工时
    SELECT name, SUM(allocated_hours) AS used_total
    FROM project_allocations
    GROUP BY name
),
no_project_allocations AS (
    -- 处理剩余未分配的时段，标记为NO PROJECT
    SELECT 
        sc.name,
        'NO PROJECT' AS project_number,
        CASE 
            WHEN uh.used_total > sc.prev_cumulative THEN 
                DATE_ADD(sc.start_time, INTERVAL (uh.used_total - sc.prev_cumulative) HOUR)
            ELSE sc.start_time
        END AS start_time,
        sc.end_time,
        sc.cumulative_block_hours - GREATEST(uh.used_total, sc.prev_cumulative) AS allocated_hours
    FROM shift_cumulative sc
    JOIN used_hours uh ON sc.name = uh.name
    JOIN total_available ta ON sc.name = ta.name
    WHERE uh.used_total < sc.cumulative_block_hours
)
-- 合并项目分配和无项目分配结果，格式化时间并排序
SELECT 
    name,
    project_number,
    DATE_FORMAT(start_time, '%H:%i') AS start_time,
    DATE_FORMAT(end_time, '%H:%i') AS end_time,
    allocated_hours AS hours_qty
FROM project_allocations
UNION ALL
SELECT 
    name,
    project_number,
    DATE_FORMAT(start_time, '%H:%i') AS start_time,
    DATE_FORMAT(end_time, '%H:%i') AS end_time,
    allocated_hours AS hours_qty
FROM no_project_allocations
WHERE allocated_hours > 0
ORDER BY name, start_time;

逻辑说明 #

1. 合并排班时段：先把表B的上午、下午班次拆分成独立的时段记录，统一格式并计算每个时段的时长。
2. 统计可用与累计工时：分别计算用户当天总可用工时，以及每个项目的累计工时（按项目编号顺序）。
3. 分配项目工时：将项目的累计工时与时段的累计时长匹配，计算每个项目在对应时段的具体起止时间和分配工时。
4. 处理剩余时段：计算已分配工时后，将剩余未使用的时段标记为NO PROJECT。
5. 结果合并：把项目分配和无项目分配的结果合并，格式化时间后按用户和时间排序输出。

注意事项 #

• 上述SQL基于MySQL语法，若使用其他数据库（如PostgreSQL），需替换对应的日期函数：
  • MySQL的STR_TO_DATE → PostgreSQL的TO_TIMESTAMP
  • MySQL的TIMESTAMPDIFF → PostgreSQL的EXTRACT(EPOCH FROM (end_time - start_time)) / 3600
  • MySQL的DATE_ADD → PostgreSQL的start_time + INTERVAL '1 hour' * (xxx)
  • MySQL的DATE_FORMAT → PostgreSQL的TO_CHAR(start_time, 'HH24:MI')
• 项目排序规则默认按project_number字符串顺序，若需自定义排序，可调整project_cumulative中的ORDER BY project_number为指定规则。

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