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
20 个解决方案
各位帮下好吗,我知道子查询会影响性能,但是这个sql我都已经想了很久才写出来,优化是在有心无力了。
好长的代码,呵呵,有执行计划吗?能确定是子查询导致速度太慢吗?
select
org.name1,
school.sch_name,
lv.lv_name,
edu.ed_name ,
count(stu.id) 入学人数,
count(case when (stu.maintenance_status = '01') then 1 end)毕业人数,
count(case when (stu.FULL_TIME = '0'and stu.maintenance_status = '01') then 1 end)非全日制毕业人数,
count(case when (stu.RETIRED_SOLDIERS = '1'and stu.maintenance_status = '01') then 1 end)退役士兵毕业人数,
count(case when (to_char(stu.LEAVE_DATE, 'mm') in ('03', '04', '05') and stu.maintenance_status = '01') then 1 end) 春季毕业人数,
wordType.Wt_Name,
wordType.Administrativelevel
from
TB_ORGANIZATION org,
TB_STU_STUDENTS_NEW stu,
TB_PROFESSION_REPORTED repo,
K_PROFESSION_LEVEL lv,
K_PROFESSION_EDUCATION_SYSTEM edu,
TB_PROFESSION_SCHOOL school ,
TB_PROFESSION_STANDARD stan,
tb_pro_standard_worktype stanType,
M_PROFESSION_WORK_TYPE wordType
where
stu.org_id = org.org_id and
repo.sch_id = school.sch_id and
stu.PROFESSIONAL_FILING_NUMBER = repo.re_id and
stan.stan_id = school.stan_id and
lv.lv_id = stan.lv_id and
edu.ed_id = stan.ed_id and
stanType.Stan_Id = stan.stan_id and
stanType.Wt_Code = wordType.Wt_Code and
stu.STAGE_ID = 11 -- 学生的审核阶段 必须是 结束
group by
org.name1,
school.sch_name,
lv.lv_name,
edu.ed_name,
wordType.Wt_Name,
wordType.Administrativelevel
现在我实现了这种结果,看似没有问题,但是查询的时候会有一个列总计功能,这个时候用的分组就麻烦了,按照这么多列分过组一计算列总计,结果就不对了,原因就是因为统计人数的时候没有加上外面的条件,真的好难
结帖,很感谢版主和yangxuan992的帮助,已经解决了,谢谢你们