SQL语句：多重条件判断语句

SQL语句：多重条件判断

根据操作时间确定工人所属班次，可用case when else 语句实现。

UPDATE warehousing  set ShiftTime=  
 (case    
 when BufferZone ='否'     
 then      
 (case          
 when time(WarehousingDate) between '06:00:00' and '13:59:59'             
 then 0    --第1班
 when time(WarehousingDate) between '14:00:00' and '21:59:59'        
 then 1     --第2班
 when (       
 (time(WarehousingDate) between '22:00:00' and '23:59:59')OR        
 (time(WarehousingDate) between '00:00:00' and '05:59:59')       
 )
 then 2     --第3班
 end)      
 else       
 (case         
 when time(BufferZDate) between '06:00:00' and '13:59:59'    
 then 0          --第1班
 when time(BufferZDate) between '14:00:00' and '21:59:59'           
 then 1      --第2班
 else 2       --第3班
 end )
 end) 

PS:
因为这里是对非主属性列进行update，而update 和 delete 都没有索引，所以要先关掉update safe mode,可用语句 set SQL_SAFE_UPDATES=0; 或 set SQL_SAFE_UPDATES=off; 实现

set SQL_SAFE_UPDATES=0;

或

set SQL_SAFE_UPDATES=off;