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')
另外你条件是不是少啊 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
这写的太复杂了,看了sql,子查询基本都一致,只是不同条件得到不同种类的数据 可以查一次表,把各个条件放到case when 中,这样得到了所有的不同种类的数据 把这个作为子查询,与其他表关联,再去取需要的其他表的数据就是这个思路
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 看看是不是你想要的
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 现在我实现了这种结果,看似没有问题,但是查询的时候会有一个列总计功能,这个时候用的分组就麻烦了,按照这么多列分过组一计算列总计,结果就不对了,原因就是因为统计人数的时候没有加上外面的条件,真的好难
非常好的样例,count 都是可以转换成 sum(case when 条件成立 then 1 else 0 end) 的。
能不能麻烦你将人数给弄上去一下,我不知道怎么改,没见过这种写法, 我从发帖那天开始到今天还没写出来还在加班,改了又改,真的很感谢你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这几个表之间相互关联 这两组表之间没联系了? 这样挺怪异的,没关系关联,就成了笛卡尔积,确定是这样的需求?
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')
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
可以查一次表,把各个条件放到case when 中,这样得到了所有的不同种类的数据
把这个作为子查询,与其他表关联,再去取需要的其他表的数据就是这个思路
能不能在具体一点?听不懂。
还有,子查询中引用了外面的stu作为条件的,如果将子查询给单独弄出来的话条件就没有了
能不能在具体一点?听不懂。
还有,子查询中引用了外面的stu作为条件的,如果将子查询给单独弄出来的话条件就没有了
还有,子查询中引用了外面的stu作为条件的,如果将子查询给单独弄出来的话条件就没有了
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
看看是不是你想要的
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
现在我实现了这种结果,看似没有问题,但是查询的时候会有一个列总计功能,这个时候用的分组就麻烦了,按照这么多列分过组一计算列总计,结果就不对了,原因就是因为统计人数的时候没有加上外面的条件,真的好难
能不能麻烦你将人数给弄上去一下,我不知道怎么改,没见过这种写法, 我从发帖那天开始到今天还没写出来还在加班,改了又改,真的很感谢你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这几个表之间相互关联
这两组表之间没联系了?
这样挺怪异的,没关系关联,就成了笛卡尔积,确定是这样的需求?