今天做工程时,遇到了case里面嵌套case的情况
直接上例子
SELECT
CASE count(*)
WHEN 0 THEN 'xx'
ELSE tuf.FOLLOW_FLAG
END
FROM
T_USER_FOLLOW AS tuf
WHERE
(
USER_ID_A = 1000000006 AND USER_ID_B = 1000000005 AND (FOLLOW_FLAG = '00' OR FOLLOW_FLAG = '02')
)
OR
(
USER_ID_A = 1000000006 AND USER_ID_B = 1000000005 AND (FOLLOW_FLAG = '01' OR FOLLOW_FLAG = '02'
)
)
如上例子的含义是:
where条件后面如果查出没有值的话,输出'xx'
如果有值的话,输出follow_flag(值为'00'、'01'、'02'当中的一个)
即总的输出值有四个:'xx'、'00'、'01'、'02'
现在的需求是:输出值中如果有'01',则将'01'改为'xx'
我的SQL改变如下
select
CASE count(*)
WHEN 0 THEN 'xx'
ELSE
CASE tuf.FOLLOW_FLAG
WHEN '01' THEN 'xx'
ELSE tuf.FOLLOW_FLAG
END
END
from T_USER_FOLLOW as tuf
where
(USER_ID_A=1000000006 and USER_ID_B=1000000005 and (FOLLOW_FLAG = '00' or FOLLOW_FLAG = '02') )
or
(USER_ID_A=1000000006 and USER_ID_B=1000000005 and (FOLLOW_FLAG = '01' or FOLLOW_FLAG = '02' ) )
即在case里面在嵌套一层cese,来判断输出值是否为'01',如果是,则将其转换成'xx';否则原值输出。