SQLserver update与多层条件判断

条件判断使用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