Question

用sql怎么计算完成率百分号

这是我写的查询语句
select
b.dept_name,
count(a.CNS_INS_ID) as xinxiyuan,
c.bianji,
count(case when a.CNS_DIAL_NUMBER > 0 then '已拨打' end) as yiwancheng,
count(case when a.CNS_DIAL_NUMBER is NULL then '未拨打' end) as weiwancheng
from LZ_COUNT_SOURCES_yue a
left join lz_department b on a.dept_id =b.dept_id
left join
(
select count(admin_id ) as bianji,dept_id from lz_admin
group by dept_id
) c on b.dept_id = c.dept_id
where a.parent_id = 60
group by b.dept_name,c.bianji

怎么算出yiwancheng除以xinxiyuan的百分比

Answer 1

select *,cast(round((yiwancheng/xinxiyuan),2)*100 as varchar(20))+'%' as baifenbi from (
select
b.dept_name,
count(a.CNS_INS_ID) as xinxiyuan,
c.bianji,
count(case when a.CNS_DIAL_NUMBER > 0 then '已拨打' end) as yiwancheng,
count(case when a.CNS_DIAL_NUMBER is NULL then '未拨打' end) as weiwancheng
from LZ_COUNT_SOURCES_yue a
left join lz_department b on a.dept_id =b.dept_id
left join
(
select count(admin_id ) as bianji,dept_id from lz_admin
group by dept_id
) c on b.dept_id = c.dept_id
where a.parent_id = 60
group by b.dept_name,c.bianji) as temp

追问

为什么算出来都是0呢，没有数

追答

那是因为你的数字可能是字符，改一下这句
cast(round((cast(yiwancheng as int)/cast(xinxiyuan as int)),2)*100 as varchar(20))+'%' as baifenbi