领导提出一个签收率需求，想要通过数据库达到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个代码运行后如下：
写出来，感觉超爽