oracle 统计

最新推荐文章于 2024-11-05 14:55:30 发布

Ccccgw

最新推荐文章于 2024-11-05 14:55:30 发布

阅读量301

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/u013850047/article/details/53381502

Oracle 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一个复杂的SQL查询案例，该查询用于从多个角度统计不同类型的员工任务完成情况，并计算特定类型任务的成功率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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