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

Question

工作优化sql

Answer 1

各位帮下好吗，我知道子查询会影响性能,但是这个sql我都已经想了很久才写出来，优化是在有心无力了。

Answer 2

 又是你，大神救我。我sql好弱

Answer 3

将when 语句去掉又快了两秒，子查询如何优化，求大神赐教

Answer 4

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')

Answer 5

另外你条件是不是少啊
stu、org、edu3个表有关联字段相互关联
stan、school、le、stan_work、workType这几个表之间相互关联
这两组表之间没联系了？
 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

Answer 6

这写的太复杂了，看了sql，子查询基本都一致，只是不同条件得到不同种类的数据
可以查一次表，把各个条件放到case when 中，这样得到了所有的不同种类的数据
把这个作为子查询，与其他表关联，再去取需要的其他表的数据就是这个思路

Answer 7

 能不能在具体一点?听不懂。
 还有，子查询中引用了外面的stu作为条件的，如果将子查询给单独弄出来的话条件就没有了

Answer 8

 能不能在具体一点?听不懂。
 还有，子查询中引用了外面的stu作为条件的，如果将子查询给单独弄出来的话条件就没有了

Answer 9

 能不能在具体一点?听不懂。
 还有，子查询中引用了外面的stu作为条件的，如果将子查询给单独弄出来的话条件就没有了

Answer 10

with stu_pep as (
select
    org.name1,
    edu.ed_name,
    sum(case when stu.maintenance_status <> '01' then 1 else null end) 入学人数,
    sum(case when stu.maintenance_status = '01'  then 1 else null end) 毕业人数,
    sum(case when stu.maintenance_status = '01'  and stu.FULL_TIME = 0 then 1 else null end) 非全日制毕业生,
    sum(case when stu.maintenance_status = '01'  and stu.RETIRED_SOLDIERS = 1 then 1 else null end) 退役士兵毕业生,
    sum(case when stu.maintenance_status = '01'  and to_char(tempStu.LEAVE_DATE, 'mm') in ('03','04','05') then 1 else null end) 春季毕业生,
from
    TB_STU_STUDENTS_NEW           stu,
    TB_ORGANIZATION               org,
    K_PROFESSION_EDUCATION_SYSTEM edu
where
    stu.org_id    = org.org_id
and edu.ed_id     = stu.school_system
and stu.STAGE_ID  = 11
group by 
    org.name1,
    edu.ed_name
)
select
    stu.name1         as name1,
    school.sch_name   as sch_name,
    le.lv_name        as lv_name,
    stu.ed_name       as ed_name,
    workType.Wt_Name  as 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               as area_level
from
    stu_pep                       stu,
    TB_PROFESSION_SCHOOL          school,
    TB_PROFESSION_STANDARD        stan,
    K_PROFESSION_LEVEL            le,
    M_PROFESSION_WORK_TYPE        worktype,
    TB_PRO_STANDARD_WORKTYPE      stan_work
where
    stan.stan_id      = school.stan_id
and le.lv_id          = stan.lv_id
and stan_work.wt_code = workType.Wt_Code
and stan_work.stan_id = stan.stan_id
看看是不是你想要的

Answer 11

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
现在我实现了这种结果,看似没有问题，但是查询的时候会有一个列总计功能，这个时候用的分组就麻烦了，按照这么多列分过组一计算列总计，结果就不对了,原因就是因为统计人数的时候没有加上外面的条件，真的好难

Answer 12

非常好的样例，count 都是可以转换成 sum(case when 条件成立 then 1 else 0 end) 的。

Answer 13

 能不能麻烦你将人数给弄上去一下，我不知道怎么改，没见过这种写法, 我从发帖那天开始到今天还没写出来还在加班，改了又改,真的很感谢你with stu_pep as (
select
    org.name1,
    edu.ed_name,
    sum(case when stu.maintenance_status <> '01' then 1 else null end) 入学人数,
    sum(case when stu.maintenance_status = '01'  then 1 else null end) 毕业人数,
    sum(case when stu.maintenance_status = '01'  and stu.FULL_TIME = 0 then 1 else null end) 非全日制毕业生,
    sum(case when stu.maintenance_status = '01'  and stu.RETIRED_SOLDIERS = 1 then 1 else null end) 退役士兵毕业生,
    sum(case when stu.maintenance_status = '01'  and to_char(tempStu.LEAVE_DATE, 'mm') in ('03','04','05') then 1 else null end) 春季毕业生,
from
    TB_STU_STUDENTS_NEW           stu,
    TB_ORGANIZATION               org,
    K_PROFESSION_EDUCATION_SYSTEM edu
where
    stu.org_id    = org.org_id
and edu.ed_id     = stu.school_system
and stu.STAGE_ID  = 11
group by 
    org.name1,
    edu.ed_name
)
select
    stu.name1         as name1,
    school.sch_name   as sch_name,
    le.lv_name        as lv_name,
    stu.ed_name       as ed_name,
    入学人数          as 入学人数,
    毕业人数          as 毕业人数,
    非全日制毕业生    as 非全日制毕业生,
    退役士兵毕业生    as 退役士兵毕业生,
    春季毕业生        as 春季毕业生,
    workType.Wt_Name  as 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               as area_level
from
    stu_pep                       stu,
    TB_PROFESSION_SCHOOL          school,
    TB_PROFESSION_STANDARD        stan,
    K_PROFESSION_LEVEL            le,
    M_PROFESSION_WORK_TYPE        worktype,
    TB_PRO_STANDARD_WORKTYPE      stan_work
where
    stan.stan_id      = school.stan_id
and le.lv_id          = stan.lv_id
and stan_work.wt_code = workType.Wt_Code
and stan_work.stan_id = stan.stan_id
另外在写sql的时候，发现stu、org、edu3个表有关联字段相互关联
stan、school、le、stan_work、workType这几个表之间相互关联
这两组表之间没联系了？
这样挺怪异的，没关系关联，就成了笛卡尔积，确定是这样的需求？

Answer 14

  结帖，很感谢版主和yangxuan992的帮助,已经解决了，谢谢你们