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;
更多推荐
SQL语句:多重条件判断语句
发布评论