Heck's Blog

    最近在维护一个几年前的项目，发现供应商报价本来为decimal类型保留两位小数的数据，结果通过ifnull后变成四舍五入保小一位小数了，排查了很久才现是由于ifnull函数引起的问题。      
SELECT
        IFNULL( (select CREATION_DATE  from  pur_bidding_supplier_line pbsl where  pbsh.HEAD_ID = pbsl.HEAD_ID  order by CREATION_DATE desc LIMIT 1 ) ,  pbsh.CREATION_DATE) as CREATION_DATE,
        IFNULL((select LINE_ID  from  pur_bidding_supplier_line pbsl where  pbsh.HEAD_ID = pbsl.HEAD_ID  order by CREATION_DATE desc LIMIT 1 ) ,0) as LINE_ID,
        CONVERT(IFNULL((select QUOTED_PRICE  from  pur_bidding_supplier_line pbsl where  pbsh.HEAD_ID = pbsl.HEAD_ID  order by CREATION_DATE desc LIMIT 1 ),99999999999999999999999),decimal(20,2)) as QUOTED_PRICE,
            pbsh.SUPPLIER_ID,
            ps.SUPPLIER_NAME,
            pe.PRO_ID ,

            ( select glook from pur_program  p  where p.pro_id=pe.PRO_ID) as GLOOK,
            ( select AGENTLOOK from  pur_program   p where p.pro_id=pe.PRO_ID) as AGENTLOOK
        FROM

            pur_bidding_supplier_head pbsh,
            pur_evaluation pe,
            pur_supplier ps
        WHERE
           pe.EVALUATION_ID = pbsh.EVALUATION_ID
          AND ps.SUPPLIER_ID = pbsh.SUPPLIER_ID
          AND pe.PRO_ID = #{0}
        ORDER BY
            pbsh.CREATION_DATE DESC

最后通过上面红色部分的convert函数来实现转成保留两位小数。
转换前的数据
2021-07-06 10:43:00  26506  88889.0  6302  李盛超测试
2021-07-06 10:47:06  26518  1500.1  6306  黄彤测试账号
2021-07-06 10:43:00  26508  6666.1  6304  顾志坚测试账号

转换后的数据
2021-07-06 10:43:00  26506  88889.02  6302  李盛超测试
2021-07-06 10:47:06  26518  1500.08  6306  黄彤测试账号
2021-07-06 10:43:00  26508  6666.05  6304  顾志坚测试账号



来源：Heck's Blog
地址：https://www.heckjj.com/post/553/
转载时须以链接形式注明作者和原始出处及本声明，否则将追究法律责任，谢谢配合！