1、格式
CASE WHEN 条件表达式1 THEN result1
WHEN 条件表达式2 THEN result2
WHEN 条件表达式3 THEN result3
[WHEN...THEN...]
ELSE resultn
END
2、说明
case when …then… else… end:整体代表一个变量字段
3、使用场景
3.1 做查询结果
select CASE WHEN CHARINDEX('(',wico07)>0 THEN SUBSTRING(wico07,1,CHARINDEX('(',wico07)-1) ELSE wico07 END
from ikh_file where wico01='21BN30559-04'
这样查询结果无名字
可以在查询时命名
select CASE WHEN CHARINDEX('(',wico07)>0 THEN SUBSTRING(wico07,1,CHARINDEX('(',wico07)-1) ELSE wico07 END as device
from ikh_file where wico01='21BN30559-04'
3.2 做查询条件
select * from igk_file,ikh_file
where
igk01='ACSSDR' and wicomsk=igkmsk and igk03='A51' and igk04='PKG size' and wico01='21BN30559-04'
AND CASE WHEN CHARINDEX('(',wico07)>0 THEN SUBSTRING(wico07,1,CHARINDEX('(',wico07)-1) ELSE wico07 END=igk02