case when一定用在select里面吗?

数据库查询中,case when经常被用在select结果部分,如:

SELECT
CASE
WHEN
vessel_type = '20000' THEN
'集装箱'
WHEN vessel_type = '30000' THEN
'液体散货'
WHEN vessel_type = '40000' THEN
'干散货'
WHEN vessel_type = '60000' THEN
'杂货'
END AS vt
FROM
sdc_dw.fm_vessel

除了应用在select返回外,case when能否用在from或where呢?带着这个疑问,做了下面几个测试。

测试1:动态from

select * from
case when 1=1 then
sdc_dw.fm_vessel
else
sdc_dw.fm_code
end
limit 1
select 1 
case when 1=1 then
from sdc_dw.fm_vessel
else
from sdc_dw.fm_code
end
limit 1

上述两段脚本执行时均报错,所以用在from是没戏了,那么用在where里面呢?

select 1 
from sdc_dw.fm_vessel
case when 1=1
then where vessel_type = '20000'
else where vessel_type = '40000'
end
limit 1

仍然报错,似乎没希望了,换个方式再试下:

select 1 
from sdc_dw.fm_vessel
where
case when 1=1
then vessel_type = '20000'
else vessel_type = '40000'
end
limit 1

成功了!

最后,上一个select和where均使用case when的示例:

SELECT
CASE
WHEN
vessel_type = '20000' THEN
'集装箱'
WHEN vessel_type = '30000' THEN
'液体散货'
WHEN vessel_type = '40000' THEN
'干散货'
WHEN vessel_type = '60000' THEN
'杂货'
END AS vt
FROM
sdc_dw.fm_vessel
WHERE
1 = 1
AND (
CASE WHEN 1 = 1
THEN vessel_type IN ( '20000', '40000' )
ELSE vessel_type IN ( '30000', '60000' )
END )
AND dwt > 20000
ORDER BY
random( )
LIMIT 100

写在最后,case when很方便,在没有mybatis辅助的情况下,也可以写出动态的结果和条件,但,不同的数据库使用方式可能不一样,上述脚本是在os" target="_blank">postgresql实例中运行的,如果你是mysql或者oracle,也可以试一试。