mysql bigdecimal 求和 会出现精度丢失问题吗 mysql的求和

mysql统计分组求和

1、由于工作需要对数据进行一个分组展示汇总处理，之前的处理情况如下：

SELECT
 a.qxmc as ‘区县’,
 count() as ‘下发数’,
 SUM(b.sflxs is not null) as ‘核查数’,
 SUM(b.sflxs is not null)/count() as ‘完成率’,
 SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1) as ‘查实数’,
 SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1)/SUM(b.sflxs is not null) as ‘查实率’,
 SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1 and b.gkcs is not null) as ‘管控数’,
 SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1 and b.gkcs is not null)/SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1) as ‘管控率’
 FROM
 t_jcxx a,t_hcryxx b where a.id=b.jcxxid
 GROUP BY
 a.qxmc

然后通过复制到Excel进行求和汇总处理，由于本人比较懒，所以想找一个直接出汇总的方法，想要的效果图如下：

百度找了很多很多用例，基本没有找到相符合业务场景的sql，后面请教了一个大佬，通过拼接的方式弄出来了：

SELECT
 a.qxmc as ‘区县’,
 count() as ‘下发数’,
 SUM(b.sflxs is not null) as ‘核查数’,
 SUM(b.sflxs is not null)/count() as ‘完成率’,
 SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1) as ‘查实数’,
 SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1)/SUM(b.sflxs is not null) as ‘查实率’,
 SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1 and b.gkcs is not null) as ‘管控数’,
 SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1 and b.gkcs is not null)/SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1) as ‘管控率’
 FROM
 t_jcxx a,t_hcryxx b where a.id=b.jcxxid
 GROUP BY
 a.qxmc
 union ALL
 select ‘汇总’,sum(a.xfs),sum(a.hcs),sum(a.wcs)/10,sum(a.css),sum(a.csl),sum(a.gks),sum(a.gkl)/10 from (
 SELECT
 a.qxmc as ‘区县’,
 count() as xfs,
 SUM(b.sflxs is not null) as hcs,
 SUM(b.sflxs is not null)/count() as wcs,
 SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1) as css,
 SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1)/SUM(b.sflxs is not null) as csl,
 SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1 and b.gkcs is not null) as gks,
 SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1 and b.gkcs is not null)/SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1) as gkl
 FROM
 t_jcxx a,t_hcryxx b where a.id=b.jcxxid
 GROUP BY
 a.qxmc
 ) a

执行结果如下：

但是这个sql太冗余了，很不喜欢，想对它进行优化，最后找了很久很久，终于让我找到了这个宝藏sql函数：COALESCE。说实话这个函数见都没见过，之前问了挺多人，他们的经验都是分组之后不能直接汇总求和了，最后竟然被这一个小小的函数解决了，果然只要想得到，就一定能找到方法，我们言归正传，优化后的sql为：

SELECT
 COALESCE(a.qxmc,‘汇总’) as ‘区县’,
 count() as ‘下发数’,
 SUM(b.sflxs is not null) as ‘核查数’,
 SUM(b.sflxs is not null)/count() as ‘完成率’,
 SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1) as ‘查实数’,
 SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1)/SUM(b.sflxs is not null) as ‘查实率’,
 SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1 and b.gkcs is not null) as ‘管控数’,
 SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1 and b.gkcs is not null)/SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1) as ‘管控率’
 FROM
 t_jcxx a,t_hcryxx b where a.id=b.jcxxid
 GROUP BY
 a.qxmc WITH ROLLUP

我们来看一下运行结果：