关于使用mysql中的div函数报错

发布于 2022-01-06 20:16:24 字数 5009 浏览 829 评论 4

数据库MySQL 5.5.27

jar包：mysql-connector-java-5.1.21.jar

mybatis-spring-1.1.1.jar

druid-0.2.10.jar

集成mybatis

使用SELECT res.create_date,res.user_ne_name,res.stmu_id,COUNT(stmu_id) as total_count FROM (
SELECT create_date,user_ne_name,SUBSTRING(parse_cell,9,(LENGTH(parse_cell)-8))DIV 4 AS stmu_id
FROM alarm_record record
WHERE record.alarm_num='3906' AND
record.create_date= ?
AND record.user_ne_name=?
) res GROUP BY res.create_date,res.user_ne_name,res.stmu_id
HAVING COUNT(stmu_id)>=10
出现下面的错误：

com.alibaba.druid.sql.parser.SQLParseException: syntax error, expect RPAREN, actual LITERAL_INT DIV
at com.alibaba.druid.sql.parser.SQLParser.accept(SQLParser.java:125)
at com.alibaba.druid.sql.parser.SQLSelectParser.parseTableSource(SQLSelectParser.java:271)
at com.alibaba.druid.sql.parser.SQLSelectParser.parseFrom(SQLSelectParser.java:262)
at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.query(MySqlSelectParser.java:182)
at com.alibaba.druid.sql.parser.SQLSelectParser.select(SQLSelectParser.java:57)
at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseSelect(MySqlStatementParser.java:179)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:84)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:68)
at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:41)
at com.alibaba.druid.filter.stat.StatFilter.mergeSql(StatFilter.java:145)
at com.alibaba.druid.filter.stat.StatFilter.createSqlStat(StatFilter.java:627)
at com.alibaba.druid.filter.stat.StatFilter.statementPrepareAfter(StatFilter.java:305)
at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:124)
at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:442)
at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:242)
at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:306)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:53)
at $Proxy97.prepareStatement(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:72)
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:82)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:54)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:70)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:56)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:141)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:101)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:95)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:355)
at $Proxy16.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:195)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:124)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:90)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:40)
at $Proxy30.query3906Alarm(Unknown Source)
at com.unicare.alarm.service.impl.AlarmRecordServiceImpl.query3906Alarm(AlarmRecordServiceImpl.java:402)
at com.unicare.quartz.service.impl.AlarmRecord_3906TimeJobServiceImpl.work(AlarmRecord_3906TimeJobServiceImpl.java:71)
at com.unicare.quartz.QuartzJobFactory.execute(QuartzJobFactory.java:31)
at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)