MySQL如何合并无空字段行 拆分排班时段避免生成冗余行

核心思路 #

通过交叉连接（CROSS JOIN）2行的临时序列，将原表每条员工排班记录自动拆分为2行，再通过条件判断生成对应拆分后的时段，单次查询即可得到目标结果，无需多次插入产生冗余数据。

适用MySQL 5.x/8.0 通用代码 #

直接查询得到目标结果 #

SELECT 
    s.staff_id,
    s.Name,
    -- 拆分周一的时段，可根据实际规则调整CASE条件
    CASE
        WHEN seg_num = 1 AND s.Monday = '8:00am-5:00pm' THEN '8:00am-1:00pm'
        WHEN seg_num = 2 AND s.Monday = '8:00am-5:00pm' THEN '2:00pm-5:00pm'
        WHEN seg_num = 1 AND s.Monday = '9:00am-6:00pm' THEN '9:00am-2:00pm'
        WHEN seg_num = 2 AND s.Monday = '9:00am-6:00pm' THEN '3:00pm-6:00pm'
    END AS Monday,
    -- 拆分周二的时段，可根据实际规则调整CASE条件
    CASE
        WHEN seg_num = 1 AND s.Tuesday = '9:00am-6:00pm' THEN '9:00am-2:00pm'
        WHEN seg_num = 2 AND s.Tuesday = '9:00am-6:00pm' THEN '3:00pm-6:00pm'
        WHEN seg_num = 1 AND s.Tuesday = '7:00am-4:00pm' THEN '7:00am-12:00pm'
        WHEN seg_num = 2 AND s.Tuesday = '7:00am-4:00pm' THEN '1:00pm-4:00pm'
    END AS Tuesday
FROM 
    你的原表名 s
CROSS JOIN (
    -- 生成2行的拆分序列，对应上下午两个时段
    SELECT 1 AS seg_num UNION ALL
    SELECT 2 AS seg_num
) AS split_segments
ORDER BY s.staff_id, seg_num;

一次性写入新表 #

如果需要将结果持久化到新表，直接在查询前加插入语句即可：

-- 先创建结构匹配的新表（可根据实际需求调整字段）
CREATE TABLE 新表名 LIKE 你的原表名;

-- 一次性插入拆分后的所有数据
INSERT INTO 新表名 (staff_id, Name, Monday, Tuesday)
SELECT 
    s.staff_id,
    s.Name,
    CASE
        WHEN seg_num = 1 AND s.Monday = '8:00am-5:00pm' THEN '8:00am-1:00pm'
        WHEN seg_num = 2 AND s.Monday = '8:00am-5:00pm' THEN '2:00pm-5:00pm'
        WHEN seg_num = 1 AND s.Monday = '9:00am-6:00pm' THEN '9:00am-2:00pm'
        WHEN seg_num = 2 AND s.Monday = '9:00am-6:00pm' THEN '3:00pm-6:00pm'
    END AS Monday,
    CASE
        WHEN seg_num = 1 AND s.Tuesday = '9:00am-6:00pm' THEN '9:00am-2:00pm'
        WHEN seg_num = 2 AND s.Tuesday = '9:00am-6:00pm' THEN '3:00pm-6:00pm'
        WHEN seg_num = 1 AND s.Tuesday = '7:00am-4:00pm' THEN '7:00am-12:00pm'
        WHEN seg_num = 2 AND s.Tuesday = '7:00am-4:00pm' THEN '1:00pm-4:00pm'
    END AS Tuesday
FROM 
    你的原表名 s
CROSS JOIN (
    SELECT 1 AS seg_num UNION ALL
    SELECT 2 AS seg_num
) AS split_segments
ORDER BY s.staff_id, seg_num;

说明 #

• 请将代码中的你的原表名、新表名替换为实际使用的表名。
• 若后续有更多拆分时段需求，只需在split_segments子查询中新增对应行即可，无需修改核心逻辑。
• 处理数千条数据仅需单次执行，无冗余插入操作，执行效率远高于多次INSERT方案。

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