17,140
社区成员




select
a.ID AS ID, a.P_ID AS P_ID, a.YEAR as YEAR, a.TERM as TERM, a.TEACHING_DATE AS TEACHING_DATE, a.WEEK_NO AS WEEK_NO, a.WEEK AS WEEK, a.TOPIC_HOUR AS TOPIC_HOUR, a.CHAPTER AS CHAPTER,
a.DISCIPLINE as DISCIPLINE, a.SAFETY_EDUCATION AS SAFETY_EDUCATION, a.MULTI_MEDIA as MULTI_MEDIA, a.MEMO AS MEMO,
b.CLASS_NAME as CLASS_NAME,
c.COURSE_NAME as COURSE_NAME,
d.ACADEMY_NAME as ACADEMY_NAME,
e.DEPT as DEPT, e.USERNAME as USERNAME,
f.AREA_CODE as AREA_CODE, f.AREA_NAME as AREA_NAME
, g.student_count
, g.ATTENDANCE_COUNT1
, g.ATTENDANCE_COUNT2
--,(nvl(g.student_count, 0) - nvl(g.ATTENDANCE_COUNT1, 0)) as ATTENDANCE_COUNT1
--,(nvl(g.student_count, 0) - nvl(g.ATTENDANCE_COUNT2, 0)) as ATTENDANCE_COUNT2
from T_TEACHING_LOG a
left join T_CLASSES b on b.CLASS_CODE = a.CLASS_CODE
left join T_COURSE c on c.COURSE_CODE = a.COURSE_CODE
left join t_specialty d on b.specialty_code = d.specialty_code
left join T_USER e on e.ID = a.U_ID
left join T_AREA f on f.AREA_CODE = a.AREA_CODE
left join (
select o.P_ID, o.WEEK_NO, o.WEEK, o.TOPIC_HOUR,
(select count(*) from S_STATUS a where a.class_code = o.class_code and a.state_code = '01') AS student_count,
(select count(*) from T_ATTENDANCE a where a.M_ID = o.ID and substr(a.topic_hour,2,1) = '1' and a.ATTENDANCE in('旷课', '事假', '病假', '公假')) AS ATTENDANCE_COUNT1,
(select count(*) from T_ATTENDANCE a where a.M_ID = o.ID and substr(a.topic_hour,2,1) = '2' and a.ATTENDANCE in('旷课', '事假', '病假', '公假')) AS ATTENDANCE_COUNT2
from T_ATTENDANCE_MAIN o
where o.year = '2012~2013' and o.term = '第二学期'
) g on g.P_ID = a.p_Id and g.WEEK_NO = a.WEEK_NO and g.WEEK = a.WEEK and g.TOPIC_HOUR = a.TOPIC_HOUR
where a.year = '2012~2013'
and a.term = '第二学期'
select o.P_ID, o.WEEK_NO, o.WEEK, o.TOPIC_HOUR,
(select count(*) from S_STATUS a where a.class_code = o.class_code and a.state_code = '01') AS student_count,
(select count(*) from T_ATTENDANCE a where a.M_ID = o.ID and substr(a.topic_hour,2,1) = '1' and a.ATTENDANCE in('旷课', '事假', '病假', '公假')) AS ATTENDANCE_COUNT1,
(select count(*) from T_ATTENDANCE a where a.M_ID = o.ID and substr(a.topic_hour,2,1) = '2' and a.ATTENDANCE in('旷课', '事假', '病假', '公假')) AS ATTENDANCE_COUNT2
from T_ATTENDANCE_MAIN o
where o.year = '2012~2013' and o.term = '第二学期'
这段不合理,大数据量表关联写在select嵌套中,会扰乱执行计划。不如先写成function试试速度。
T_ATTENDANCE.M_ID有索引的吧?