昨天写一个查询,要求取到一些时间字段到现在的时间段,于是想到在where 后面写case when语句把时间分段,但是在实际实验中发现,当参数满足了第二条when表达式之后,还会继续满足后面的when表达式,甚至于满足所有的when表达式。于是把问题抽象一下,写成了下面的SQL语句,发现结果变得非常有趣:
/*Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
**Edit date: 2011-08-16
*/
with a as
(select '2010.01' as c1, 'a' as c2, 4 as c3 from dual
union all
select '1999.10' as c1, 'b' as c2, 5 as c3 from dual
union all
select '1970.01' as c1, 'c' as c2, 6 as c3 from dual)
select case
when MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) < 20 then '10'
when MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) >= 20 and MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) < 400 then '20'
when MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) >= 400 and MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) < 600 then '30'
else '0'
end flag,
a.* from a
where 'AL' = 'ALL' or '20' = case
when MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) < 20 then '10'
when MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) >= 20 and MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) < 400 then '20'
when MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) >= 400 and MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) < 600 then '30'
else '0'
end
运行结果如下:
flag c1 c2 c3
20 1999.10 b 5
30 1970.01 c 6
可以看到,当第二个when表达式的返回结果‘20’正好满足条件,但是运行之后却把第二和第三个when的结果全查询出来了。
经过反复修改,发现把前面并未执行的 ‘AL’ = 'ALL' or 注释之后,结果正确;或者把第一个when 的表达式改为
MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) < 20 and MONTHS_BETWEEN(SYSDATE,TO_DATE(a.c1, 'YYYY.MM')) > 0
结果也会正确;把后面的else语句去掉或者改为 else null 返回空值结果也会正确。但是偏偏这个看上去并没有什么语法或者逻辑错误的SQL语句会执行不正确。
经过和同事的研究,还是不知所以然,因为这个问题好像在9i和11g的版本上执行时并不会出现,也许,这是一个蛋疼的BUG吧……