更新时(update)使用自定义变量
加入火车行驶方向用0和1表示,0方向的id要为基数,1方向的id要为偶数,为了更新id,使用自定义变量。
– 当direction = 0方向时:
set @num:=-1;
update section_copy set id = (@num:= @num+2) where direction = 0 order by linecode,sectioncode asc ;
– 当direction = 1方向时:
set @num:=0;
update section_copy set id = (@num:= @num+2) where direction = 1 order by linecode,sectioncode asc ;
update中嵌入case函数和自定义变量
若现在火车运行轨道按照每一段路的上行下行进行标记0和1,下行为从北到南或从东到西,反之下行,可能在某一段路为连续上行或下行,这样就必须依赖direction字段的值,若为偶数时,则id为奇数,若为奇数时,则id为偶数。
– 当diretion为偶数时:
set @num:=-1;
update section_copy
set id =
CASE
WHEN mod (direction, 2) =0 THEN
(@num:= @num+2)
END
WHERE direction = 0
order by sectioncode asc;
– 当diretion为奇数时:
set @num:=0;
update section_copy
set id =
CASE
WHEN mod (direction, 2) !=0 THEN
(@num:= @num+2)
END
WHERE direction = 0
order by sectioncode asc;