<em>mysql</em> float double类型 1.float类型float列类型默认长度查不到结果，必须指定精度，比如 num float, insert into table (num) values (0.12); select * from table where num=0.12的话，empty set。num float(9,7), insert into table (num)
在写SQL里面总会遇到数据类型的问题，这里讲一下cast的用法:
1、类型转换
MySQL 的CAST()函数可用来获取一个类型的值，并产生另一个类型的值
cast()函数是一个转换函数，参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。
CAST(value as type);就是CAST(xxx AS 类型)
可以转换的类型是有...
大家帮忙看看，急等，虽然效果也能出来，，但是不知道为什么后台的日志会报错，，最后4c币了，，大家帮忙看看，，充了之后再给补
SELECT TRUNCATE(10.5996,2) as tadou
,TRUNCATE(10.5996,2) as tales
,TRUNCATE(10.5996,2) as amount
FROM settle；
com.alibaba.druid.sql.parser.ParserException: ERROR. token : TRUNCATE, pos : 15
at com.alibaba.druid.sql.parser.SQLExprParser.primary(SQLExprParser.java:567)
at com.alibaba.druid.sql.dialect.<em>mysql</em>.parser.MySqlExprParser.primary(MySqlExprParser.java:166)
at com.alibaba.druid.sql.parser.SQLExprParser.expr(SQLExprParser.java:121)
at com.alibaba.druid.sql.parser.SQLExprParser.parseSelectItem(SQLExprParser.java:1827)
at com.alibaba.druid.sql.parser.SQLSelectParser.parseSelectList(SQLSelectParser.java:295)
at com.alibaba.druid.sql.dialect.<em>mysql</em>.parser.MySqlSelectParser.query(MySqlSelectParser.java:134)
at com.alibaba.druid.sql.parser.SQLSelectParser.select(SQLSelectParser.java:60)
at com.alibaba.druid.sql.dialect.<em>mysql</em>.parser.MySqlStatementParser.parseSelect(MySqlStatementParser.java:215)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:180)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:145)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:140)
at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:53)
at com.alibaba.druid.filter.stat.StatFilter.mergeSql(StatFilter.java:145)
at com.alibaba.druid.filter.stat.StatFilter.createSqlStat(StatFilter.java:630)
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:448)
at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:342)
at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:323)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:53)
at com.sun.proxy.$Proxy23.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 org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:59)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:355)
at com.sun.proxy.$Proxy9.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:160)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:95)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:40)
at com.sun.proxy.$Proxy14.selectSumSalesTadou(Unknown Source)
at com.tadu.bdrc.copyright.service.Impl.SalesReportServiceImpl.selectSumSalesTadou(SalesReportServiceImpl.java:45)
at com.tadu.bdrc.copyright.controller.SalesReportController.selectTotalsalesPage(SalesReportController.java:581)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:746)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:687)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:915)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:822)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:796)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:218)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:442)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1082)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:623)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:744)
情景：项目中遇到统计的一些查询，结果就会遇到带有<em>小数</em>的结果，一般要求对结果进行<em>四舍五入</em>保留两位<em>小数</em>。
解决方式有以下两种：
方式一：使用 round(m,2)函数 //注：m为所需处理字段值，2为保留几位<em>小数</em>
select round(AVG(score),2) as average from student
//round(89.457,2) 平均数89.457 <em>四舍五入</em>89.46
...
方式一：SELECT FORMAT(12562.6655,2);结果：12,562.67SELECT FORMAT(12332.1,4);结果：12,332.1000（<em>小数</em>没有数字会补0）查看文档：Formats the number X to a format like ‘#,###,###.##’, rounded to D decimal places, and returns the res
一、ROUND()函数用法
ROUND(X) -- 表示将值 X <em>四舍五入</em>为整数，无<em>小数</em>位
ROUND(X,D) -- 表示将值 X <em>四舍五入</em>为<em>小数</em>点后 D 位的数值，D为<em>小数</em>点后<em>小数</em>位数。若要保留 X 值<em>小数</em>点左边的 D 位，可将 D 设为负值。
该函数与FLOOR函数有所不同，在应用中需注意，另外，DECIMAL函数也有类似该函数<em>四舍五入</em>的功能。
1、ROUND示例：...
(假设我们有一个students表，表中有个varchar类型的number字段）
第一次我使用round（）函数找出<em>四舍五入</em>后等于其本身的数字：
SELECT number FROM students WHERE ROUND(number) = number;
但是这种方法存在一个问题，就是无法剔除100.000这种情况
之后我使用了not like "%.%"的方法找出
Think in Java（中文版）电子书 学习JAVA语言提高必看..
相关下载链接：[url=//download.csdn.net/download/howard_liu1314/4307478?utm_source=bbsseo]//download.csdn.net/download/howard_liu1314/4307478?utm_source=bbsseo[/url]
