SELECT
*
FROM
table1
WHERE
column1 <= 30
GROUP BY
column4
HAVING
CASE
WHEN column3 = 1 THEN
column2 <= 96 ELSE column2 <= 500
END
原有sql正常执行,现代码需求加入group by column4, mybatis框架,table1数据量百万,加入分组后以上复杂sql无法解析
错误信息:
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "CASE" "CASE"
at line 5, column 10.
初步搜索判断是分页插件和jsqlparser的问题,试图在pom.xml中加入jsqlparser依赖,无果,高版本jsqlparser错误信息略有变化。看到这一条:Caused by: net.sf.jsqlparser.parser.ParseException_你大佬!的博客-CSDN博客
基本认定与其继续寻找jar包问题不如修改sql,复习where与having区别
SQL中where与having的区别_WD技术的博客-CSDN博客_sql having和where的区别
果然最大的区别就是使用聚集或者group by,针对当前应用场景,果断将case when改到where下,如下,一样报错
SELECT
*
FROM
table1
WHERE
column1 <= 30
AND
CASE
WHEN column3 = 1 THEN
column2 <= 96 ELSE column2 <= 500
END
GROUP BY
column4
再次查询where下case when使用方法,发现问题所在,修改case语句如下,正常运行,速度未见明显影响。
where条件中使用case when是什么鬼? - 墨天轮
SELECT
*
FROM
table1
WHERE
column1 <= 30
AND
(CASE
WHEN column3 = 1 AND column2 <= 96 THEN 1
WHEN column3 = 1 AND column2 <= 500 THEN 1
ELSE 0
END) = 1
GROUP BY
column4