基于指定起始SortID按周轮换的人员任务分配实现咨询

核心需求拆解 #

你需要实现按周循环的人员任务分配，核心逻辑可梳理为：

1. 固定4名人员对应SortID 0-3，初始排序从指定的起始SortID开始
2. 每自然周整体人员顺序后移1位，超出末尾的人员自动补到队首
3. 同一周内所有日期的任务分配完全一致，排序结果依次对应MissionA到MissionD

基础表说明 #

sort表（人员排序映射表） #

实现方案 #

方案1：SQL直接生成数据落库（适合批量写入assign表场景） #

以MySQL语法为例，可直接运行生成符合要求的assign表数据：

-- 自定义配置参数
SET @start_sort_id = 2; -- 起始SortID
SET @start_date = '2021-01-03'; -- 任务分配开始日期
SET @end_date = '2021-01-11'; -- 任务分配结束日期
SET @total_user = (SELECT COUNT(*) FROM sort); -- 自动获取总人数，无需硬编码

-- 写入assign表
INSERT INTO assign (Date, Week, MissionA, MissionB, MissionC, MissionD)
SELECT 
    t.cur_date AS Date,
    t.week_num AS Week,
    MAX(CASE WHEN s.week_order = 0 THEN s.Name END) AS MissionA,
    MAX(CASE WHEN s.week_order = 1 THEN s.Name END) AS MissionB,
    MAX(CASE WHEN s.week_order = 2 THEN s.Name END) AS MissionC,
    MAX(CASE WHEN s.week_order = 3 THEN s.Name END) AS MissionD
FROM (
    -- 生成连续日期与对应周数
    SELECT 
        DATE_ADD(@start_date, INTERVAL seq.seq DAY) AS cur_date,
        FLOOR(DATEDIFF(DATE_ADD(@start_date, INTERVAL seq.seq DAY), @start_date)/7) + 1 AS week_num
    FROM (
        -- 数字辅助表生成0-99的序列，可覆盖3个月的日期范围
        SELECT a.N + b.N * 10 AS seq FROM 
        (SELECT 0 N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
        (SELECT 0 N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
    ) seq
    WHERE DATE_ADD(@start_date, INTERVAL seq.seq DAY) <= @end_date
) t
CROSS JOIN (
    -- 计算每个人员当周的排序位置
    SELECT 
        SortID,
        Name,
        (SortID - (@start_sort_id + t.week_num - 1) + @total_user) % @total_user AS week_order
    FROM sort
) s
GROUP BY t.cur_date, t.week_num;

方案2：后端代码生成（适合灵活输出、无需直接落库的场景） #

以Python为例：

import datetime
from datetime import timedelta

# 基础配置
start_sort_id = 2
start_date = datetime.date(2021, 1, 3)
end_date = datetime.date(2021, 1, 11)
# 读取sort表数据
sort_data = [{"SortID":0,"Name":"A"}, {"SortID":1,"Name":"B"}, {"SortID":2,"Name":"C"}, {"SortID":3,"Name":"D"}]
total_user = len(sort_data)
name_list = [x["Name"] for x in sorted(sort_data, key=lambda k:k["SortID"])]

assign_result = []
cur_date = start_date
while cur_date <= end_date:
    # 计算当前周数
    day_diff = (cur_date - start_date).days
    week_num = day_diff // 7 + 1
    # 计算当周偏移量，旋转得到任务序列
    offset = (start_sort_id + week_num - 1) % total_user
    week_mission = name_list[offset:] + name_list[:offset]
    # 组装结果
    assign_result.append({
        "Date": cur_date.strftime("%Y/%m/%d"),
        "Week": week_num,
        "MissionA": week_mission[0],
        "MissionB": week_mission[1],
        "MissionC": week_mission[2],
        "MissionD": week_mission[3]
    })
    cur_date += timedelta(days=1)

# 后续可根据需求打印结果或写入数据库

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