如何解决SQL中CASE语句使用BETWEEN报missing keyword错误

错误原因 #

你使用的是简单CASE表达式语法，该语法仅支持等值匹配，不能直接在WHEN关键字后接BETWEEN范围判断语句，因此触发missing keyword语法错误。

修复代码 #

直接改用搜索CASE表达式即可实现范围判断，可用代码如下：

select first_name,
       case
         when trunc(months_between(sysdate, hire_date) / 12) between 1 and 5 then
          '1'
         when trunc(months_between(sysdate, hire_date) / 12) between 6 and 10 then
          '2'
         when trunc(months_between(sysdate, hire_date) / 12) between 11 and 15 then
          '3'
         else
          '4'
       end as information
  from employees;

优化写法（可选） #

如果想要避免重复计算工龄，可提前计算工龄字段后再做判断，代码可读性更高：

with emp_work_year as (
  select 
    first_name,
    trunc(months_between(sysdate, hire_date) / 12) as work_year
  from employees
)
select 
  first_name,
  case
    when work_year between 1 and 5 then '1'
    when work_year between 6 and 10 then '2'
    when work_year between 11 and 15 then '3'
    else '4'
  end as information
from emp_work_year;

内容的提问来源于stack exchange，提问作者rerettttt