如何在数据库排班表中插入循环重复的A/B/C/D班组值？

核心思路 #

通过给每个班次按时间顺序生成连续序号，再对序号取模4，将余数映射为'A'/'B'/'C'/'D'，实现循环赋值。

场景1：查询时生成shift_name（不修改表结构） #

适用于仅需要查询展示结果的场景，以下是主流数据库的实现语句：

MySQL 8.0+/PostgreSQL/SQL Server

SELECT 
    shift_starts_dt,
    shift_type,
    CASE 
        WHEN rn % 4 = 1 THEN 'A'
        WHEN rn % 4 = 2 THEN 'B'
        WHEN rn % 4 = 3 THEN 'C'
        ELSE 'D'
    END AS shift_name
FROM (
    -- 先按班次时间排序生成连续序号
    SELECT 
        shift_starts_dt,
        shift_type,
        ROW_NUMBER() OVER (ORDER BY shift_starts_dt) AS rn
    FROM your_table_name  -- 替换为你的表名
) AS ranked_shifts;

场景2：新增字段并批量赋值（修改表结构） #

如果需要永久在表中添加shift_name字段并填充数据，分两步操作：

步骤1：新增字段

ALTER TABLE your_table_name ADD COLUMN shift_name CHAR(1);

步骤2：批量更新数据

MySQL 8.0+

WITH ranked_shifts AS (
    SELECT 
        shift_starts_dt,
        shift_type,
        ROW_NUMBER() OVER (ORDER BY shift_starts_dt) AS rn
    FROM your_table_name
)
UPDATE your_table_name t
JOIN ranked_shifts rs 
    ON t.shift_starts_dt = rs.shift_starts_dt 
    AND t.shift_type = rs.shift_type
SET t.shift_name = CASE 
    WHEN rs.rn % 4 = 1 THEN 'A'
    WHEN rs.rn % 4 = 2 THEN 'B'
    WHEN rs.rn % 4 = 3 THEN 'C'
    ELSE 'D'
END;

PostgreSQL

UPDATE your_table_name t
SET shift_name = CASE 
    WHEN rs.rn % 4 = 1 THEN 'A'
    WHEN rs.rn % 4 = 2 THEN 'B'
    WHEN rs.rn % 4 = 3 THEN 'C'
    ELSE 'D'
END
FROM (
    SELECT 
        shift_starts_dt,
        shift_type,
        ROW_NUMBER() OVER (ORDER BY shift_starts_dt) AS rn
    FROM your_table_name
) rs
WHERE t.shift_starts_dt = rs.shift_starts_dt 
  AND t.shift_type = rs.shift_type;

说明 #

• 假设shift_starts_dt和shift_type的组合是唯一标识，若表中有主键，建议用主键关联更新，避免重复数据导致错误。
• 排序依据ORDER BY shift_starts_dt确保班次按时间顺序排列，符合每日Day、Night的顺序要求。

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