本人微信公众号"aeolian"~

sql语句中处理金额,把分换算成元

问题,sql语句中直接将金额/100返回的结果会有多个小数位。

select cate.cate_name as name,sum(acc.money)/100 as value from account as acc left join conCategory as cate on acc.cateCode=cate.cate_code 
group by cate_name

《sql语句中处理金额,把分换算成元》

FORMAT(money/100,2)

select cate.cate_name as name,FORMAT(sum(acc.money)/100,2) as value from account as acc left join conCategory as cate on acc.cateCode=cate.cate_code 
group by cate_name

保留两位小数,但是千位会有逗号有逗号的话echarts之类的会不识别这个数据

《sql语句中处理金额,把分换算成元》

truncate(money,2)

直接截取小数点后两位,不进行四舍五入

select cate.cate_name as name,truncate(sum(acc.money)/100,2) as value from account as acc left join conCategory as cate on acc.cateCode=cate.cate_code 
group by cate_name

OK

《sql语句中处理金额,把分换算成元》

 

点赞

Leave a Reply

Your email address will not be published. Required fields are marked *