条件判断使用IF…ELSE...或CASE...WHEN...THEN...
if用于代码块,case用于增删查改内部
想要根据多列修改某列值,就需要多层判断。
UPDATE #a
SET [修改列] =
CASE
WHEN [第1层条件] THEN
CASE WHEN [第2层条件] THEN '值1'
WHEN [第2层条件] THEN '值2'
WHEN [第2层条件] THEN '值3'
END
WHEN [第1层条件] THEN
CASE WHEN [第2层条件] THEN '值4'
WHEN [第2层条件] THEN '值5'
WHEN [第2层条件] THEN '值6'
END
END
使用iif同样能达到效果。(iif是在增删查改内部使用)
iif(第一层判断,iif(第二层判断,内容A,内容B),iif(第二层判断,内容C,内容D))
PS:[列名] in (x,y,z,a,b,c) 也算判断条件
列如举例,两层判断修改
UPDATE #a
SET [hou_num] =
CASE
WHEN mon_num in(1,7,8,12) THEN
CASE WHEN hou_num in('0','1','2','3','4','5','6','7','11','12','22','23') THEN 'low'
WHEN hou_num in('8','17','18','19','20','21') THEN 'mid'
WHEN hou_num in('9','10','13','14','15','16') THEN 'hig'
END
WHEN mon_num in(2,3,4,5,6,9,10,11) THEN
CASE WHEN hou_num in('0','1','2','3','4','5','6','7','11','12','22','23') THEN 'low'
WHEN hou_num in('8','13','14','17','18','19','20','21') THEN 'mid'
WHEN hou_num in('9','10','15','16') THEN 'hig'
END
END