select a.emp_id,
bsu.emp_no,
bsu.emp_name,
bo.name,
sp0 + sp1 + sp2 + sp3 + sp5 + sp6 + sp7 + sp8 + sp9 + sp10 as tot_cnt,
sp,
sp0,
sp1,
sp2,
sp3,
sp5,
sp6,
sp7,
sp8,
sp9,
sp10,
case
when sp > 0 and sp0 > 0 then
to_char(sp0 / sp * 100, '999') || '%'
else
'0%'
end p
from (select emp_id,
count(1) tot_cnt,
count(case
when task_type = 1 or task_type = 4 then
1
end) sp,
count(case
when task_type in ('1', '4') and result_comment = '通过' then
1
end) sp0,
count(case
when task_type = 2 or task_type = 6 then
1
end) sp1,
count(case
when task_type = 3 or task_type = 8 or task_type = 9 then
1
end) sp2,
count(case
when task_type = 5 then
1
end) sp3,
count(case
when task_type = 10 or task_type = 101 then
1
end) sp5,
count(case
when task_type = 11 then
1
end) sp6,
count(case
when task_type = 12 then
1
end) sp7,
count(case
when task_type = 13 then
1
end) sp8,
count(case
when task_type = 14 then
1
end) sp9,
count(case
when task_type = 7 then
1
end) sp10
from mess_task
where status = 3
and org_id in (select org_id from base_organization)
group by emp_id) a,
base_sys_user bsu,
base_organization bo
where a.emp_id = bsu.emp_id
and bsu.org_id = bo.org_id
order by emp_id
bsu.emp_no,
bsu.emp_name,
bo.name,
sp0 + sp1 + sp2 + sp3 + sp5 + sp6 + sp7 + sp8 + sp9 + sp10 as tot_cnt,
sp,
sp0,
sp1,
sp2,
sp3,
sp5,
sp6,
sp7,
sp8,
sp9,
sp10,
case
when sp > 0 and sp0 > 0 then
to_char(sp0 / sp * 100, '999') || '%'
else
'0%'
end p
from (select emp_id,
count(1) tot_cnt,
count(case
when task_type = 1 or task_type = 4 then
1
end) sp,
count(case
when task_type in ('1', '4') and result_comment = '通过' then
1
end) sp0,
count(case
when task_type = 2 or task_type = 6 then
1
end) sp1,
count(case
when task_type = 3 or task_type = 8 or task_type = 9 then
1
end) sp2,
count(case
when task_type = 5 then
1
end) sp3,
count(case
when task_type = 10 or task_type = 101 then
1
end) sp5,
count(case
when task_type = 11 then
1
end) sp6,
count(case
when task_type = 12 then
1
end) sp7,
count(case
when task_type = 13 then
1
end) sp8,
count(case
when task_type = 14 then
1
end) sp9,
count(case
when task_type = 7 then
1
end) sp10
from mess_task
where status = 3
and org_id in (select org_id from base_organization)
group by emp_id) a,
base_sys_user bsu,
base_organization bo
where a.emp_id = bsu.emp_id
and bsu.org_id = bo.org_id
order by emp_id