工作中遇到的sql有关问题，一个sql查询一万多条数据花了8秒钟,求大神帮优化一下

工作中遇到的sql问题，一个sql查询一万多条数据花了8秒钟,求大神帮优化一下

  
                
                select org.name1,
                       school.sch_name,
                       le.lv_name,
                       edu.ed_name,
                       (select count(tempStu.id)
                          from TB_STU_STUDENTS_NEW tempStu
                         where tempStu.STAGE_ID = 11
                           and tempStu.PROFESSIONAL_FILING_NUMBER =
                               stu.PROFESSIONAL_FILING_NUMBER
                           and tempStu.maintenance_status <> '01') 入学人数,
                       (select count(tempStu.id)
                          from TB_STU_STUDENTS_NEW tempStu
                         where tempStu.maintenance_status = '01'
                           and tempStu.STAGE_ID = 11
                           and tempStu.PROFESSIONAL_FILING_NUMBER =
                               stu.PROFESSIONAL_FILING_NUMBER) 毕业人数,
                       (select count(tempStu.id)
                          from TB_STU_STUDENTS_NEW tempStu
                         where tempStu.maintenance_status = '01'
                           and tempStu.STAGE_ID = 11
                           and tempStu.PROFESSIONAL_FILING_NUMBER =
                               stu.PROFESSIONAL_FILING_NUMBER
                           and tempStu.FULL_TIME = 0) 非全日制毕业生,
                       (select count(tempStu.id)
                          from TB_STU_STUDENTS_NEW tempStu
                         where tempStu.maintenance_status = '01'
                           and tempStu.STAGE_ID = 11
                           and tempStu.PROFESSIONAL_FILING_NUMBER =
                               stu.PROFESSIONAL_FILING_NUMBER
                           and tempStu.RETIRED_SOLDIERS = 1) 退役士兵毕业生,
                       (select count(tempStu.id)
                          from TB_STU_STUDENTS_NEW tempStu
                         where tempStu.maintenance_status = '01'
                           and tempStu.STAGE_ID = 11
                           and tempStu.PROFESSIONAL_FILING_NUMBER =
                               stu.PROFESSIONAL_FILING_NUMBER
                           and (to_char(tempStu.LEAVE_DATE, 'mm') = '03' or
                               to_char(tempStu.LEAVE_DATE, 'mm') = 04 or
                               to_char(tempStu.LEAVE_DATE, 'mm') = '05')) 春季毕业生,
                       workType.Wt_Name,
                       case
                         when workType.Administrativelevel = '0' then
                          '未知'
                         when workType.Administrativelevel = '1' then
                          '省级'
                         when workType.Administrativelevel = '2' then
                          '市级'
                         when workType.Administrativelevel = '3' then
                          '公共'
                         when workType.Administrativelevel = '4' then
                          '其他'
                       end
                  from TB_STU_STUDENTS_NEW           stu,
                       TB_PROFESSION_SCHOOL          school,
                       TB_ORGANIZATION               org,
                       TB_PROFESSION_STANDARD        stan,
                       K_PROFESSION_LEVEL            le,
                       K_PROFESSION_EDUCATION_SYSTEM edu,
                       M_PROFESSION_WORK_TYPE        workType,
                       tb_pro_standard_worktype      stan_work
                 where stu.org_id = org.org_id
                   and stu.maintenance_status = '01'
                   and stu.STAGE_ID = 11
                   and stan.stan_id = school.stan_id
                   and le.lv_id = stan.lv_id
                   and edu.ed_id = stu.school_system
                   and stan_work.wt_code = workType.Wt_Code
                   and stan_work.stan_id = stan.stan_id
                     
                     
------解决思路----------------------
好长的代码，呵呵，有执行计划吗？能确定是子查询导致速度太慢吗？
------解决思路----------------------
这写的太复杂了，看了sql，子查询基本都一致，只是不同条件得到不同种类的数据
可以查一次表，把各个条件放到case when 中，这样得到了所有的不同种类的数据
把这个作为子查询，与其他表关联，再去取需要的其他表的数据

就是这个思路

引用:

1、把相关子查询用一个查询把统计信息都统计出来，然后再与其他表关联

select PROFESSIONAL_FILING_NUMBER,
count(case when tempStu.maintenance_status <> '01'  then 1 end) 入学人数,
count(case when ……  then 1 end) 非全日制毕业生,
……
 from TB_STU_STUDENTS_NEW tempStu
where tempStu.STAGE_ID = 11
group by PROFESSIONAL_FILING_NUMBER

2、
to_char(tempStu.LEAVE_DATE, 'mm') = '03' or
                               to_char(tempStu.LEAVE_DATE, 'mm') = 04 or
                               to_char(tempStu.LEAVE_DATE, 'mm') = '05'
这个条件改为in
to_char(tempStu.LEAVE_DATE, 'mm') in ('03' ,'04','05')