with user_passed_exams as (
-- 查询技术复核类型的考试信息
select '技术复核' as exam_type,
t.user_id,
t.emp_num,
t.exam_code,
t.exam_name,
-- 子查询:计算当前考试类型下有效的考试总数
(select count(1)
from tpl_lookup_item_t li
where li.classify_code = 'INTERVIEW_EXAM_INFO'
and li.status = '1'
and li.item_attr2 = '有效'
and instr(li.item_attr4, '技术复核') > 0) as required_count
from omp_exam_record_t t
left join tpl_lookup_item_t li
on li.classify_code = 'INTERVIEW_EXAM_INFO'
and li.status = '1'
and li.item_code = t.exam_code
where t.is_pass = '1'
and instr(li.item_attr4, '技术复核') > 0
and t.user_id is not null
group by t.user_id, emp_num, t.exam_code, t.exam_name
union all
-- 查询综合复核类型的考试信息
select '综合复核' as exam_type,
t.user_id,
t.emp_num,
t.exam_code,
t.exam_name,
-- 子查询:计算当前考试类型下有效的考试总数
(select count(1)
from tpl_lookup_item_t li
where li.classify_code = 'INTERVIEW_EXAM_INFO'
and li.status = '1'
and li.item_attr2 = '有效'
and instr(li.item_attr4, '综合复核') > 0) as required_count
from omp_exam_record_t t
left join tpl_lookup_item_t li
on li.classify_code = 'INTERVIEW_EXAM_INFO'
and li.status = '1'
and li.item_code = t.exam_code
where t.is_pass = '1'
and instr(li.item_attr4, '综合复核') > 0
and t.user_id is not null
group by t.user_id, emp_num, t.exam_code, t.exam_name),
-- 对每个用户在每种考试类型下的通过考试数量进行统计
-- 并聚合通过的考试编码和名称
user_passed_count as (select user_id,
emp_num,
exam_type,
count(*) as passed_count,
required_count,
listagg(exam_code, ',') within group (order by exam_code) as passed_exam_codes,
listagg(exam_name, ',') within group (order by exam_code) as passed_exam_names
from user_passed_exams
group by user_id, emp_num, exam_type, required_count),
-- 判断用户是否已全部通过所需考试,并标记状态(pass_status)
user_pass_status as (select upc.exam_type,
upc.user_id,
upc.emp_num,
upc.passed_count,
upc.required_count,
case
when upc.passed_count >= upc.required_count then '已全部通过'
else '未全部通过'
end as pass_status,
upc.passed_exam_codes,
upc.passed_exam_names
from user_passed_count upc
where (upc.user_id, upc.exam_type) in (
-- 子查询:筛选出因未通过考试而被自动失效的用户记录
select log.user_id as userid,
log.interview_type as interviewtype
from omp_emp_reviewer_log_t log
where log.operate_type like '%自动失效%'
and log.operate_cause like '%复核官没有通过考试或者没有参加考试,系统自动失效资质%'
and (log.user_id, log.interview_type) in (
-- 子查询:获取维护状态为“失效”的复核人员信息
select a.user_id as userid,
a.interview_type as interviewtype
from omp_emp_reviewer_pool_t a
where a.vindicate_type = '失效')
order by log.creation_date desc)),
-- 筛选出两种考试类型都已通过的用户
user_pass_exit as (select a.user_id,
(select t1.lname from tpl_user_t t1 where t1.lname like concat('%',a.emp_num,'%')) as w3name,
a.emp_num,
a.exam_type,
a.passed_count,
a.required_count,
a.pass_status,
a.passed_exam_codes,
a.passed_exam_names,
g.full_name_zh as deptname,
org_v.l1_org_name as bg,
org_v.l2_org_name as bu,
org_v.l3_org_name as pdu,
org_v.l4_org_name as develop,
(select t1.lname from tpl_user_t t1 where t1.user_id = b.created_by) as created_by,
(select t2.lname from tpl_user_t t2 where t2.user_id = b.last_updated_by) as last_updated_by
from user_pass_status a
left join omp_emp_reviewer_pool_t b
on a.user_id = b.user_id and a.exam_type = b.interview_type and
b.vindicate_type = '失效'
left join omp_local_org_t org_v on org_v.id = b.local_org_id
left join omp_org_t g on g.org_code = b.dept_id and g.is_vaild = 'Y'
where pass_status = '已全部通过')
-- 最终查询:筛选出两种考试类型都已通过的用户
select *
from user_pass_exit;
上述SQL中的tpl_user_t 是子查询,是否可以优化下