如何用单条SQL语句正确排序白班与夜班员工的任务？

问题背景 #

现有夜班（emp_id='1'）和白班（emp_id='2'）员工的任务数据如下：

// 夜班员工
id  from_time    to_time   task
1    21:00:00    22:00:00 - Cleaning(some task)
1    22:00:00    23:30:00 - Fumigation(can be some other task also)
1    4:00:00     7:00:00 - Disinfection
1    2:00:00     4:00:00 - Break
1    23:30:00    2:00:00 - Fogging

// 白班员工
2  09:00:00    10:00:00 - Cleaning(some task)
2  16:00:00    18:30:00 - Disinfection
2  11:30:00    14:00:00 - Fumigation(can be some other task also)
2  14:00:00    16:00:00 - Fogging
2  10:00:00    11:30:00 - Break

需求是按from_time对任务进行升序排序，但现有SQL只能分别处理单班次：

• 仅对白班有效的语句：

SELECT * FROM testing WHERE emp_id='2' ORDER BY from_time ASC

• 仅对夜班有效的语句：

SELECT * FROM testing WHERE emp_id='1'
ORDER BY 
  CASE 
    WHEN CAST(from_time AS TIME) > '12:00:00' THEN 1
    ELSE 2
  END,
  CAST(from_time AS TIME) ASC;

需要一条能同时正确排序两类员工任务的SQL语句。

解决方案 #

通过结合员工ID（或班次标识）和时间条件，动态调整排序优先级，实现统一排序：

SELECT * 
FROM testing 
ORDER BY 
  -- 针对夜班员工，先排12点后的时段，再排凌晨时段；白班员工统一按正常时间排序
  CASE 
    WHEN emp_id = '1' AND CAST(from_time AS TIME) > '12:00:00' THEN 0
    WHEN emp_id = '1' THEN 1
    ELSE 2
  END,
  CAST(from_time AS TIME) ASC;

逻辑说明 #

• 夜班员工（emp_id='1'）：将from_time大于12:00的任务优先级设为0（排在前面），凌晨时段（≤12:00）设为1（排在后面），再按from_time升序，最终得到符合夜班工作流程的排序：21:00→22:00→23:30→2:00→4:00→7:00。
• 白班员工（emp_id='2'）：优先级统一设为2，直接按from_time升序，得到白班正常工作顺序：09:00→10:00→11:30→14:00→16:00。

扩展优化 #

如果后续用专门的shift字段（如'night'/'day'）替代emp_id区分班次，只需将emp_id = '1'替换为shift = 'night'即可，扩展性更强：

SELECT * 
FROM testing 
ORDER BY 
  CASE 
    WHEN shift = 'night' AND CAST(from_time AS TIME) > '12:00:00' THEN 0
    WHEN shift = 'night' THEN 1
    ELSE 2
  END,
  CAST(from_time AS TIME) ASC;

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