问题遇到的现象和发生背景
在sql中编辑好sql 但是移植到mybatisplus xml 中使用时报错
问题相关代码，请勿粘贴截图
<![CDATA[
SELECT *,SUM(countSum) as countSum1 from (
SELECT
count_sum as countSum ,
elt(INTERVAL (count_sum,0, 8, 16, 48 ),
'8','9-16','17-48','48+'
) AS countSumLevel,
FROM_UNIXTIME(createtime, '%Y-%m-%d'),
DATEDIFF(FROM_UNIXTIME(endtime, '%Y-%m-%d'),FROM_UNIXTIME(createtime, '%Y-%m-%d')) as days,
elt(
INTERVAL (
DATEDIFF(
FROM_UNIXTIME(endtime, '%Y-%m-%d'),
FROM_UNIXTIME(createtime, '%Y-%m-%d')
),0, 2, 4, 6,8,10,15 ),
'1-2','3-4','5-6',
'7-8','9-10','10-15','15+'
) AS daysLevel,
CONCAT_WS('/',elt(INTERVAL (count_sum,0, 8, 16, 48 ),'8','9-16','17-48','48+'),elt(INTERVAL (DATEDIFF(
FROM_UNIXTIME(endtime, '%Y-%m-%d'),
FROM_UNIXTIME(createtime, '%Y-%m-%d')),0, 2, 4, 6,8,10,15 ),'1-2','3-4','5-6','7-8','9-10','10-15','15+')) AS CountSumDay
FROM
tb_order
WHERE
endtime !=''
ORDER BY CountSum DESC
) as a
GROUP BY CountSumDay
]]>
运行结果及报错内容
Caused by: org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: SELECT *,SUM(countSum) as countSum1 from (
SELECT
count_sum as countSum ,
elt(INTERVAL (count_sum,0, 8, 16, 48 ),
'8','9-16','17-48','48+'
) AS countSumLevel,
FROM_UNIXTIME(createtime, '%Y-%m-%d'),
DATEDIFF(dd,FROM_UNIXTIME(endtime, '%Y-%m-%d'),FROM_UNIXTIME(createtime, '%Y-%m-%d')) as days,
elt(
INTERVAL (
DATEDIFF(
FROM_UNIXTIME(endtime, '%Y-%m-%d'),
FROM_UNIXTIME(createtime, '%Y-%m-%d')
),0, 2, 4, 6,8,10,15 ),
'1-2','3-4','5-6',
'7-8','9-10','10-15','15+'
) AS daysLevel,
CONCAT_WS('/',elt(INTERVAL (count_sum,0, 8, 16, 48 ),
'8','9-16','17-48','48+'
) ,elt(
INTERVAL (
DATEDIFF(dd,
FROM_UNIXTIME(endtime, '%Y-%m-%d'),
FROM_UNIXTIME(createtime, '%Y-%m-%d')
),0, 2, 4, 6,8,10,15 ),
'1-2','3-4','5-6','7-8','9-10','10-15','15+'
)) AS CountSumDay
FROM
tb_order
WHERE
endtime !=''
ORDER BY CountSum DESC
) as a
GROUP BY CountSumDay
### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: SELECT *,SUM(countSum) as countSum1 from (
SELECT
count_sum as countSum ,
elt(INTERVAL (count_sum,0, 8, 16, 48 ),
'8','9-16','17-48','48+'
) AS countSumLevel,
FROM_UNIXTIME(createtime, '%Y-%m-%d'),
DATEDIFF(dd,FROM_UNIXTIME(endtime, '%Y-%m-%d'),FROM_UNIXTIME(createtime, '%Y-%m-%d')) as days,
elt(
INTERVAL (
DATEDIFF(
FROM_UNIXTIME(endtime, '%Y-%m-%d'),
FROM_UNIXTIME(createtime, '%Y-%m-%d')
),0, 2, 4, 6,8,10,15 ),
'1-2','3-4','5-6',
'7-8','9-10','10-15','15+'
) AS daysLevel,
CONCAT_WS('/',elt(INTERVAL (count_sum,0, 8, 16, 48 ),
'8','9-16','17-48','48+'
) ,elt(
INTERVAL (
DATEDIFF(dd,
FROM_UNIXTIME(endtime, '%Y-%m-%d'),
FROM_UNIXTIME(createtime, '%Y-%m-%d')
),0, 2, 4, 6,8,10,15 ),
'1-2','3-4','5-6','7-8','9-10','10-15','15+'
)) AS CountSumDay
FROM
tb_order
WHERE
endtime !=''
ORDER BY CountSum DESC
) as a
GROUP BY CountSumDay
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) ~[mybatis-3.5.7.jar:3.5.7]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:153) ~[mybatis-3.5.7.jar:3.5.7]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145) ~[mybatis-3.5.7.jar:3.5.7]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.7.jar:3.5.7]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_322]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_322]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_322]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_322]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427) ~[mybatis-spring-2.0.6.jar:2.0.6]
... 130 common frames omitted
Caused by: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: SELECT *,SUM(countSum) as countSum1 from (
SELECT
count_sum as countSum ,
elt(INTERVAL (count_sum,0, 8, 16, 48 ),
'8','9-16','17-48','48+'
) AS countSumLevel,
FROM_UNIXTIME(createtime, '%Y-%m-%d'),
DATEDIFF(dd,FROM_UNIXTIME(endtime, '%Y-%m-%d'),FROM_UNIXTIME(createtime, '%Y-%m-%d')) as days,
elt(
INTERVAL (
DATEDIFF(
FROM_UNIXTIME(endtime, '%Y-%m-%d'),
FROM_UNIXTIME(createtime, '%Y-%m-%d')
),0, 2, 4, 6,8,10,15 ),
'1-2','3-4','5-6',
'7-8','9-10','10-15','15+'
) AS daysLevel,
CONCAT_WS('/',elt(INTERVAL (count_sum,0, 8, 16, 48 ),
'8','9-16','17-48','48+'
) ,elt(
INTERVAL (
DATEDIFF(dd,
FROM_UNIXTIME(endtime, '%Y-%m-%d'),
FROM_UNIXTIME(createtime, '%Y-%m-%d')
),0, 2, 4, 6,8,10,15 ),
'1-2','3-4','5-6','7-8','9-10','10-15','15+'
)) AS CountSumDay
FROM
tb_order
WHERE
endtime !=''
ORDER BY CountSum DESC
) as a
GROUP BY CountSumDay
at com.baomidou.mybatisplus.core.toolkit.ExceptionUtils.mpe(ExceptionUtils.java:39) ~[mybatis-plus-core-3.4.3.1.jar:3.4.3.1]
at com.baomidou.mybatisplus.extension.parser.JsqlParserSupport.parserSingle(JsqlParserSupport.java:52) ~[mybatis-plus-extension-3.4.3.1.jar:3.4.3.1]
at com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor.beforeQuery(TenantLineInnerInterceptor.java:65) ~[mybatis-plus-extension-3.4.3.1.jar:3.4.3.1]
at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:78) ~[mybatis-plus-extension-3.4.3.1.jar:3.4.3.1]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62) ~[mybatis-3.5.7.jar:3.5.7]
at com.sun.proxy.$Proxy302.query(Unknown Source) ~[na:na]
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:143) ~[pagehelper-5.1.2.jar:na]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62) ~[mybatis-3.5.7.jar:3.5.7]
at com.sun.proxy.$Proxy302.query(Unknown Source) ~[na:na]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151) ~[mybatis-3.5.7.jar:3.5.7]
... 137 common frames omitted
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered " "(" "( "" at line 4, column 12.
Was expecting one of:
"AS" ...
"DO" ...
"ANY" ...
"KEY" ...
"PERCENT" ...
"END" ...
"INTO" ...
"FROM" ...
"OPEN" ...
"TABLE" ...
"WHERE" ...
"FOR" ...
"XML" ...
"UNION" ...
"GROUP" ...
"ORDER" ...
"VALUE" ...
"HAVING" ...
"INSERT" ...
"VALUES" ...
"REPLACE" ...
"TRUNCATE" ...
"INTERSECT" ...
"CAST" ...
"EXCEPT" ...
"MINUS" ...
"OVER" ...
"PARTITION" ...
"EXTRACT" ...
"MATERIALIZED" ...
"START" ...
"CONNECT" ...
"PRIOR" ...
"SIBLINGS" ...
"COLUMN" ...
"NULLS" ...
"FIRST" ...
"LAST" ...
"ROWS" ...
"RANGE" ...
"FOLLOWING" ...
"ROW" ...
"COMMIT" ...
"SEPARATOR" ...
"CASCADE" ...
"NO" ...
"ACTION" ...
<K_DATETIMELITERAL> ...
"PRECISION" ...
<S_IDENTIFIER> ...
<S_QUOTED_IDENTIFIER> ...
"," ...
")" ...
"|" ...
"&" ...
"^" ...
"::" ...
"ORDER" ...
"ORDER" ...
at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:16395) ~[jsqlparser-1.0.jar:na]
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:16248) ~[jsqlparser-1.0.jar:na]
at net.sf.jsqlparser.parser.CCJSqlParser.FromItem(CCJSqlParser.java:3435) ~[jsqlparser-1.0.jar:na]
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:2445) ~[jsqlparser-1.0.jar:na]
at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:2579) ~[jsqlparser-1.0.jar:na]
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:2336) ~[jsqlparser-1.0.jar:na]
at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:2328) ~[jsqlparser-1.0.jar:na]
at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:129) ~[jsqlparser-1.0.jar:na]
at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:80) ~[jsqlparser-1.0.jar:na]
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:54) ~[jsqlparser-1.0.jar:na]
at com.baomidou.mybatisplus.extension.parser.JsqlParserSupport.parserSingle(JsqlParserSupport.java:49) ~[mybatis-plus-extension-3.4.3.1.jar:3.4.3.1]
... 145 common frames omitted
我的解答思路和尝试过的方法
在sql中添加cdata ，或者在sql中能正常运行并返回，检查sql没有问题，但是在xml中会报错
在sql中运行的结果
我想要达到的结果
正常返回数据
