时间戳

首页 > 解决方案 > 如何减少我的 sql 的基数和成本

sql - 如何减少我的 sql 的基数和成本

问题描述

在此处输入图像描述 以上是我只想降低基数成本的结果。
我有多个子查询我想减少基数成本我应该怎么做。我试图用 with 子句来做，但它不会根据我的要求设置查询执行得很好，但基数和成本要高得多，这就是为什么我要拆分基数

select distinct
C.CLASS_ID,
C.TEACHER_ID,

C.SHIFT_ID,
C.BRANCH_ID,
T.TEACHER_NAME,
ns.nazim_id,
c.class_uid,
(Select count(*) AS total_class from student where upper(class_id)=upper(C.CLASS_ID) and is_active_flg='Y'
and status='STUDYING'
and upper(class_days) like '%'||TO_CHAR(to_date(:P28_DATE),'DY')||'%') as Total_Students,

(select count(*) from student
where class_id=C.CLASS_ID and is_active_flg='Y') as Active_Students,


(select count(*) from student
where class_id=C.CLASS_ID and is_active_flg='N') as InActive_Students,

(Select count(*) as Marked from student_class_attend where upper(class_id)=C.CLASS_ID
and attend_date =:P28_DATE) as Attend_Marked,

(select count(*)
from student
where class_id=C.CLASS_ID and is_active_flg='Y' and gr_number not in (select gr_number from student_class_attend where attend_date=:P28_DATE)
) as Attend_UnMarked,

(select count(*) from student_class_attend
where class_id=C.CLASS_ID and attend_date=:P28_DATE and submit_flg='Y') as attend_submitted,

(select count(*) from student_class_attend
where class_id=C.CLASS_ID and attend_date=:P28_DATE and submit_flg='N') as attend_pending


--qc.unconfirmed
from CLASS C
--join class_time ct on c.class_uid = ct.class_uid
LEFT JOIN student_class_attend SC ON sc.class_id=c.class_id
join nazim_shift ns on decode(:V_GENDER,'BOTH',c.gender,ns.gender) = c.gender
and instr(ns.department_id||':',c.department_id||':')>0
and instr(ns.branch_id||':',c.branch_id||':')>0
and instr(ns.shift_id||':',c.shift_id||':')>0
and ns.nazim_login_id = lower(trim(:APP_USER))
--join nazim_shift_time nst on ns.nazim_shift_uid = nst.nazim_shift_uid
--and nst.class_time=ct.class_time
left join TEACHER T ON C.TEACHER_ID = T.TEACHER_ID and t.active_flg = 'Y'
where c.active_flg = 'Y'
order by c.class_id
;

标签： sqloracle

解决方案

由于您已经加入了 student_class_attend。您可以将 Attend_Marked 和 Attend_submitted 的相关子查询替换为窗口函数。

,SUM(CASE WHEN sc.attend_date =:P28_DATE THEN 1 else 0 end) OVER (PARTITION BY sc.CLASS_ID)as Attend_Marked,
,SUM(CASE WHEN sc.attend_date=:P28_DATE and sc.submit_flg='Y' THEN 1 else 0 end) OVER (PARTITION BY sc.CLASS_ID) as attend_submitted,

您也可以加入学生并为其他子查询做类似的事情。您可以在此处阅读有关分析功能的信息

推荐阅读