计算订单签收率的sql查询思路与过程(涉及百分比和四舍五入)
领导提出一个签收率需求，想要通过数据库达到excel中表现的形式，提高计算速度和工作效率，
如下形式：
数据库中表数据结构：
部分数据如下：
sql语句思路如下：
-- 1.已签收：以物流反馈管道，状态分组，已签收出现的次数 select `直发签收率计算表`.`物流反馈管道`,`直发签收率计算表`.`状态`,count(`直发签收率计算表`.`状态`) FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道` ,`直发签收率计算表`.`状态` HAVING `直发签收率计算表`.`状态`=\'已签收\' -- 2.拒收： select `直发签收率计算表`.`物流反馈管道`,`直发签收率计算表`.`状态`,count(`直发签收率计算表`.`状态`) FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道` ,`直发签收率计算表`.`状态` HAVING `直发签收率计算表`.`状态`=\'拒收\' -- 3.在途： select `直发签收率计算表`.`物流反馈管道`,`直发签收率计算表`.`状态`,count(`直发签收率计算表`.`状态`) FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道` ,`直发签收率计算表`.`状态` HAVING `直发签收率计算表`.`状态`=\'在途\' -- 4.未发货： select `直发签收率计算表`.`物流反馈管道`,`直发签收率计算表`.`状态`,count(`直发签收率计算表`.`状态`) FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道` ,`直发签收率计算表`.`状态` HAVING `直发签收率计算表`.`状态`=\'未发货\' -- 5.总计：各物流反馈管道出现的次数，即已签收+拒收+在途+未发货,即总计 select `直发签收率计算表`.`物流反馈管道`,count(`直发签收率计算表`.`物流反馈管道`) as \'总计\' FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道` -- 6.各渠道签收率，签收/(签收+拒收),即签收/已完成 select y.`物流反馈管道`,y.count1/(y.count1+j.count2) as \'签收/已完成\' from (select `直发签收率计算表`.`物流反馈管道`,`直发签收率计算表`.`状态`,count(`直发签收率计算表`.`状态`)as count1 FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道` ,`直发签收率计算表`.`状态` HAVING `直发签收率计算表`.`状态`=\'已签收\') y left join (select `直发签收率计算表`.`物流反馈管道`,`直发签收率计算表`.`状态`,count(`直发签收率计算表`.`状态`) as count2 FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道` ,`直发签收率计算表`.`状态` HAVING `直发签收率计算表`.`状态`=\'拒收\') j on y.`物流反馈管道`=j.`物流反馈管道` -- 7.各渠道签收率，签收/(已签收+拒收+在途+未发货),即签收/总计 select y.`物流反馈管道`,y.county/z.countz as \'签收/总计\' from (select `直发签收率计算表`.`物流反馈管道`,`直发签收率计算表`.`状态`,count(`直发签收率计算表`.`状态`)as county FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道` ,`直发签收率计算表`.`状态` HAVING `直发签收率计算表`.`状态`=\'已签收\') y left join (select `直发签收率计算表`.`物流反馈管道`,count(`直发签收率计算表`.`物流反馈管道`) as countz FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道`) z on y.`物流反馈管道` = z.`物流反馈管道` -- 8.各渠道签收率,(签收+拒收)/总计 select y.`物流反馈管道`,(y.county+j.countj)/z.countz as \'已完成/总计\' from (select `直发签收率计算表`.`物流反馈管道`,`直发签收率计算表`.`状态`,count(`直发签收率计算表`.`状态`)as county FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道` ,`直发签收率计算表`.`状态` HAVING `直发签收率计算表`.`状态`=\'已签收\') y left join (select `直发签收率计算表`.`物流反馈管道`,`直发签收率计算表`.`状态`,count(`直发签收率计算表`.`状态`) as countj FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道` ,`直发签收率计算表`.`状态` HAVING `直发签收率计算表`.`状态`=\'拒收\') j on y.`物流反馈管道`=j.`物流反馈管道` left join (select `直发签收率计算表`.`物流反馈管道`,count(`直发签收率计算表`.`物流反馈管道`) as count0 FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道`) z on y.`物流反馈管道` = z.`物流反馈管道` -- --------------------------------------- -- 9.物流反馈管道签收率总表，不带百分号 select y.`物流反馈管道`,y.county as \'已签收\',j.countj as \'拒收\',t.countt as \'在途\',w.countw as \'未发货\',z.countz as \'z总计\',y.county/(y.county+j.countj) as \'签收/已完成\', y.county/z.countz as \'签收/总计\',(y.county+j.countj)/z.countz as \'已完成/总计\' from (select `直发签收率计算表`.`物流反馈管道`,`直发签收率计算表`.`状态`,count(`直发签收率计算表`.`状态`) as county FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道` ,`直发签收率计算表`.`状态` HAVING `直发签收率计算表`.`状态`=\'已签收\') y left join (select `直发签收率计算表`.`物流反馈管道`,`直发签收率计算表`.`状态`,count(`直发签收率计算表`.`状态`) as countj FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道` ,`直发签收率计算表`.`状态` HAVING `直发签收率计算表`.`状态`=\'拒收\') j on y.`物流反馈管道`=j.`物流反馈管道` left join (select `直发签收率计算表`.`物流反馈管道`,`直发签收率计算表`.`状态`,count(`直发签收率计算表`.`状态`) countt FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道` ,`直发签收率计算表`.`状态` HAVING `直发签收率计算表`.`状态`=\'在途\' ) t on t.`物流反馈管道`=y.`物流反馈管道` left join (select `直发签收率计算表`.`物流反馈管道`,`直发签收率计算表`.`状态`,count(`直发签收率计算表`.`状态`) countw FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道` ,`直发签收率计算表`.`状态` HAVING `直发签收率计算表`.`状态`=\'未发货\') w on w.`物流反馈管道`=y.`物流反馈管道` left join (select `直发签收率计算表`.`物流反馈管道`,count(`直发签收率计算表`.`物流反馈管道`) countz FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道`) z on z.`物流反馈管道`=y.`物流反馈管道` -- ---------------------------------------------------------------------------------------------------- -- 10.物流反馈管道签收率总表，带百分号 select y.`物流反馈管道`,y.county as \'已签收\',j.countj as \'拒收\',t.countt as \'在途\',w.countw as \'未发货\',z.countz as \'z总计\', concat(left(y.county/(y.county+j.countj)*100,5),\'%\') as \'签收/已完成\', concat(left(y.county/z.countz*100,5),\'%\') as \'签收/总计\', concat(left((y.county+j.countj)/z.countz*100,5),\'%\') as \'已完成/总计\' from (select `直发签收率计算表`.`物流反馈管道`,`直发签收率计算表`.`状态`,count(`直发签收率计算表`.`状态`) as county FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道` ,`直发签收率计算表`.`状态` HAVING `直发签收率计算表`.`状态`=\'已签收\') y left join (select `直发签收率计算表`.`物流反馈管道`,`直发签收率计算表`.`状态`,count(`直发签收率计算表`.`状态`) as countj FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道` ,`直发签收率计算表`.`状态` HAVING `直发签收率计算表`.`状态`=\'拒收\') j on y.`物流反馈管道`=j.`物流反馈管道` left join (select `直发签收率计算表`.`物流反馈管道`,`直发签收率计算表`.`状态`,count(`直发签收率计算表`.`状态`) countt FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道` ,`直发签收率计算表`.`状态` HAVING `直发签收率计算表`.`状态`=\'在途\' ) t on t.`物流反馈管道`=y.`物流反馈管道` left join (select `直发签收率计算表`.`物流反馈管道`,`直发签收率计算表`.`状态`,count(`直发签收率计算表`.`状态`) countw FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道` ,`直发签收率计算表`.`状态` HAVING `直发签收率计算表`.`状态`=\'未发货\') w on w.`物流反馈管道`=y.`物流反馈管道` left join (select `直发签收率计算表`.`物流反馈管道`,count(`直发签收率计算表`.`物流反馈管道`) countz FROM `直发签收率计算表` group by `直发签收率计算表`.`物流反馈管道`) z on z.`物流反馈管道`=y.`物流反馈管道` -- 11.已签收，拒收，在途，未发货的总数： -- 已签收总数 select count(`直发签收率计算表`.`状态`) as \'已签收总数\' FROM `直发签收率计算表` where `直发签收率计算表`.`状态`=\'已签收\' -- 拒收总数 select count(`直发签收率计算表`.`状态`) as \'拒收总数\' FROM `直发签收率计算表` where `直发签收率计算表`.`状态`=\'拒收\' -- 在途总数 select count(`直发签收率计算表`.`状态`) as \'在途总数\' FROM `直发签收率计算表` where `直发签收率计算表`.`状态`=\'在途\' ---行列互换后： -- 已签收，拒收，在途，未发货的总数 select count(case when `直发签收率计算表`.`状态` = \'已签收\' then `直发签收率计算表`.`物流反馈管道` end) as \'已签收总数\', count(case when `直发签收率计算表`.`状态` = \'拒收\' then `直发签收率计算表`.`物流反馈管道` end) as \'拒收总数\', count(case when `直发签收率计算表`.`状态` = \'在途\' then `直发签收率计算表`.`物流反馈管道` end) as \'在途总数\', count(case when `直发签收率计算表`.`状态` = \'未发货\' then `直发签收率计算表`.`物流反馈管道` end) as \'未发货总数\', count(*) as \'总计\' from `直发签收率计算表` -- 12.已签收，拒收，在途，未发货的总数，各比率均值 select \'平均值总计\', count(case when `直发签收率计算表`.`状态` = \'已签收\' then `直发签收率计算表`.`物流反馈管道` end) as \'已签收总数\', count(case when `直发签收率计算表`.`状态` = \'拒收\' then `直发签收率计算表`.`物流反馈管道` end) as \'拒收总数\', count(case when `直发签收率计算表`.`状态` = \'在途\' then `直发签收率计算表`.`物流反馈管道` end) as \'在途总数\', count(case when `直发签收率计算表`.`状态` = \'未发货\' then `直发签收率计算表`.`物流反馈管道` end) as \'未发货总数\', count(*) as \'总计\', count(case when `直发签收率计算表`.`状态` = \'已签收\' then `直发签收率计算表`.`物流反馈管道` end)/count(*) \'签收/总数平均值\', count(case when `直发签收率计算表`.`状态` = \'已签收\' then `直发签收率计算表`.`物流反馈管道` end)/(count(case when `直发签收率计算表`.`状态` = \'已签收\' then `直发签收率计算表`.`物流反馈管道` end) +count(case when `直发签收率计算表`.`状态` = \'拒收\' then `直发签收率计算表`.`物流反馈管道` end)) as \'签收/已完成平均值\', (count(case when `直发签收率计算表`.`状态` = \'已签收\' then `直发签收率计算表`.`物流反馈管道` end)+count(case when `直发签收率计算表`.`状态` = \'拒收\' then `直发签收率计算表`.`物流反馈管道` end))/count(*) as \'已完成/总计平均值\' from `直发签收率计算表` -- ---------------------------- -- 13.已签收，拒收，在途，未发货的总数，各比率均值,分数以百分号形式 select \'平均值总计\', count(case when `直发签收率计算表`.`状态` = \'已签收\' then `直发签收率计算表`.`物流反馈管道` end) as \'已签收总数\', count(case when `直发签收率计算表`.`状态` = \'拒收\' then `直发签收率计算表`.`物流反馈管道` end) as \'拒收总数\', count(case when `直发签收率计算表`.`状态` = \'在途\' then `直发签收率计算表`.`物流反馈管道` end) as \'在途总数\', count(case when `直发签收率计算表`.`状态` = \'未发货\' then `直发签收率计算表`.`物流反馈管道` end) as \'未发货总数\', count(*) as \'总计\', concat(left(count(case when `直发签收率计算表`.`状态` = \'已签收\' then `直发签收率计算表`.`物流反馈管道` end)/count(*)*100,5),\'%\') as \'签收/总数平均值\', concat(left(count(case when `直发签收率计算表`.`状态` = \'已签收\' then `直发签收率计算表`.`物流反馈管道` end)/(count(case when `直发签收率计算表`.`状态` = \'已签收\' then `直发签收率计算表`.`物流反馈管道` end) +count(case when `直发签收率计算表`.`状态` = \'拒收\' then `直发签收率计算表`.`物流反馈管道` end))*100,5),\'%\') as \'签收/已完成平均值\', concat(left((count(case when `直发签收率计算表`.`状态` = \'已签收\' then `直发签收率计算表`.`物流反馈管道` end)+count(case when `直发签收率计算表`.`状态` = \'拒收\' then `直发签收率计算表`.`物流反馈管道` end))/count(*)*100,5),\'%\') as \'已完成/总计平均值\' from `直发签收率计算表`
-- 1.以改派表为例，四舍五入到万分位，物流反馈管道签收率总表，带百分号 select y.`物流反馈管道`,y.county as \'已签收\',j.countj as \'拒收\',t.countt as \'在途\',w.countw as \'未发货\',z.countz as \'z总计\', concat(left(ROUND(y.county/(y.county+j.countj),4)*100,5),\'%\') as \'签收/已完成\', concat(left(ROUND(y.county/z.countz,4)*100,5),\'%\') as \'签收/总计\', concat(left(ROUND((y.county+j.countj)/z.countz,4)*100,5),\'%\') as \'已完成/总计\' from (select `改派签收率计算表`.`物流反馈管道`,`改派签收率计算表`.`状态`,count(`改派签收率计算表`.`状态`) as county FROM `改派签收率计算表` group by `改派签收率计算表`.`物流反馈管道` ,`改派签收率计算表`.`状态` HAVING `改派签收率计算表`.`状态`=\'已签收\') y left join (select `改派签收率计算表`.`物流反馈管道`,`改派签收率计算表`.`状态`,count(`改派签收率计算表`.`状态`) as countj FROM `改派签收率计算表` group by `改派签收率计算表`.`物流反馈管道` ,`改派签收率计算表`.`状态` HAVING `改派签收率计算表`.`状态`=\'拒收\') j on y.`物流反馈管道`=j.`物流反馈管道` left join (select `改派签收率计算表`.`物流反馈管道`,`改派签收率计算表`.`状态`,count(`改派签收率计算表`.`状态`) countt FROM `改派签收率计算表` group by `改派签收率计算表`.`物流反馈管道` ,`改派签收率计算表`.`状态` HAVING `改派签收率计算表`.`状态`=\'在途\' ) t on t.`物流反馈管道`=y.`物流反馈管道` left join (select `改派签收率计算表`.`物流反馈管道`,`改派签收率计算表`.`状态`,count(`改派签收率计算表`.`状态`) countw FROM `改派签收率计算表` group by `改派签收率计算表`.`物流反馈管道` ,`改派签收率计算表`.`状态` HAVING `改派签收率计算表`.`状态`=\'未发货\') w on w.`物流反馈管道`=y.`物流反馈管道` left join (select `改派签收率计算表`.`物流反馈管道`,count(`改派签收率计算表`.`物流反馈管道`) countz FROM `改派签收率计算表` group by `改派签收率计算表`.`物流反馈管道`) z on z.`物流反馈管道`=y.`物流反馈管道` -- 2.以改派表为例，已签收，拒收，在途，未发货的总数，各比率均值,分数以百分号形式，四舍五入到万分位 select \'平均值总计\', count(case when `改派签收率计算表`.`状态` = \'已签收\' then `改派签收率计算表`.`物流反馈管道` end) as \'已签收总数\', count(case when `改派签收率计算表`.`状态` = \'拒收\' then `改派签收率计算表`.`物流反馈管道` end) as \'拒收总数\', count(case when `改派签收率计算表`.`状态` = \'在途\' then `改派签收率计算表`.`物流反馈管道` end) as \'在途总数\', count(case when `改派签收率计算表`.`状态` = \'未发货\' then `改派签收率计算表`.`物流反馈管道` end) as \'未发货总数\', count(*) as \'总计\', concat(left(ROUND(count(case when `改派签收率计算表`.`状态` = \'已签收\' then `改派签收率计算表`.`物流反馈管道` end)/count(*),4)*100,5),\'%\') as \'签收/总数平均值\', concat(left(ROUND(count(case when `改派签收率计算表`.`状态` = \'已签收\' then `改派签收率计算表`.`物流反馈管道` end)/(count(case when `改派签收率计算表`.`状态` = \'已签收\' then `改派签收率计算表`.`物流反馈管道` end) +count(case when `改派签收率计算表`.`状态` = \'拒收\' then `改派签收率计算表`.`物流反馈管道` end)),4)*100,5),\'%\') as \'签收/已完成平均值\', concat(left(ROUND((count(case when `改派签收率计算表`.`状态` = \'已签收\' then `改派签收率计算表`.`物流反馈管道` end)+count(case when `改派签收率计算表`.`状态` = \'拒收\' then `改派签收率计算表`.`物流反馈管道` end))/count(*),4)*100,5),\'%\') as \'已完成/总计平均值\' from `改派签收率计算表`
其中第10个代码运行如下：
第13个代码运行后如下：
写出来，感觉超爽
版权声明：本文为pingzizhuanshu原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。