如何减少Oracle SQL临时表用量，优化查询并保留单学生单行数据

需求：每个学生对应一行数据。当前查询过度使用临时表拆分数据，导致速度极慢甚至系统崩溃；尝试直接关联考试分数表加CASE语句，却出现学生多行数据的问题。以下是原查询及尝试代码，附优化方案：

原查询代码 #

Select st.schoolID, st.student_number, st.lastfirst, st.grade_level, sat.numscore as SAT, psat.numscore as PSAT, msci.numscore as MStempScience, msoc.numscore as MStempSoc,
t4.failcount, t1.poabs, t1.popre,
round(((t1.popre - (t1.poabs + (case when t2.xtra_att>=0 then t2.xtra_att else 0 end)))/(case when t1.popre <>0 then t1.popre end))*100,1)||'%' as Attendance_Rate,
CASE WHEN round(((t1.popre - (t1.poabs + (case when t2.xtra_att>=0 then t2.xtra_att else 0 end)))/(case when t1.popre <>0 then t1.popre end))*100,1) < 90 then 'YES' else 'NO' end as ChronicAbsence,
CASE WHEN t3.discount > 0 THEN 'YES' ELSE 'NO' END as Supspended,
CASE WHEN smi.flagspeced = 1 THEN 'YES' ELSE 'NO' END as SPECED,
CASE WHEN smi.flaglep = 1 THEN 'YES' ELSE 'NO' END as ELL,
CASE WHEN st.lunchstatus in('F','R') THEN 'YES' ELSE 'NO' END as FRED
from students st
left outer join storedgrades sg on sg.studentID = st.ID
inner join S_MI_STU_GC_X smi on smi.studentsdcid = st.dcid
inner join U_def_ext_students ext on ext.studentsDCID = st.dcid
inner join (select psmd.studentID, sum(periods_absent) as poabs, sum(potential_periods_present) as popre

from ps_membership_defaults psmd
where calendardate between '05-SEP-22' and '09-NOV-22'          
group by studentID
)t1 on t1.studentID = st.ID

left outer join (      
select att.studentID, Sum(Case when  ATT_Code in ('TDY','ETD','WTH') and att.schoolid<25  then .14289  when  ATT_Code in ('TDY','ETD','WTH') and att.schoolid in (28,38,40) then .0278 end) as xtra_att
from Attendance att
INNER JOIN attendance_code attc ON att.ATTENDANCE_CODEID = attc.ID AND att.SCHOOLID = attc.SCHOOLID AND att.YEARID = attc.YEARID
where att.att_date between '05-SEP-22' and '09-NOV-22'  --change to param
group by att.studentID
)t2 on t2.studentID = st.ID 
         
left outer join (
select l.studentID,  sum(Case WHEN l.consequence in ('SPNI', 'SPNO') THEN '1' ELSE '0' END) as discount
from log l
where l.schoolID = 28
and l.discipline_incidentdate between '05-SEP-22' and '09-NOV-22'   
group by l.studentID
)t3 on t3.studentID = st.ID

left outer join (
select st.ID, sum(CASE WHEN (pgf.grade = 'F'  and pgf.finalgradename in ('S1', 'S2') ) then 1 else 0 END) as failcount
from students st
inner join pgfinalgrades pgf on pgf.studentid=st.id
where st.grade_level = 12
and st.enroll_status = 0
and pgf.startdate  >= '05-SEP-22'
and st.schoolID = 28
group by st.ID
 )t4 on t4.ID = st.ID
 
left outer join (
select  st.ID, sts.numscore
from studenttestscore sts
inner join students st on sts.studentID = st.ID
inner join testscore ts on ts.ID = sts.testscoreID 
inner join studenttest stest on stest.id = sts.studenttestid
where ts.id = 105
and ts.testID = 1
and stest.test_date between '01-JAN-22' and '09-NOV-22' 
and st.schoolID = 28
and st.grade_level = 12
) sat on sat.ID = st.ID

left outer join (
select  st.ID, sts.numscore
from studenttestscore sts
inner join students st on sts.studentID = st.ID
inner join testscore ts on ts.ID = sts.testscoreID 
inner join studenttest stest on stest.id = sts.studenttestid
where ts.id = 155 
and ts.testID = 103
and stest.test_date between '01-JAN-22' and '09-NOV-22' 
and st.schoolID = 28
and st.grade_level = 12
)psat on psat.ID = st.ID

left outer join(
select  st.ID, sts.numscore
from studenttestscore sts
inner join students st on sts.studentID = st.ID
inner join testscore ts on ts.ID = sts.testscoreID 
inner join studenttest stest on stest.id = sts.studenttestid
where ts.ID = 53
and ts.testID = 3
and stest.test_date between '01-JAN-22' and '09-NOV-22' 
and st.schoolID = 28
and st.grade_level = 12
)msci on msci.ID = st.ID

left outer join (
select  st.ID, sts.numscore
from studenttestscore sts
inner join students st on sts.studentID = st.ID
inner join testscore ts on ts.ID = sts.testscoreID 
inner join studenttest stest on stest.id = sts.studenttestid
where ts.ID = 54
and ts.testID = 3
and stest.test_date between '01-JAN-22' and '09-NOV-22' 
and st.schoolID = 28
and st.grade_level = 12
)msoc on msoc.ID = st.ID

where st.grade_level = 12
and st.enroll_status = 0
and st.schoolid = 28
group by st.student_number, st.schoolID, st.ID, smi.flagatrisk, st.lastfirst, st.grade_level, t1.poabs, t1.popre, t2.xtra_att, t3.discount, smi.flagspeced, smi.flaglep, st.lunchstatus,  t4.failcount, sat.numscore, psat.numscore, msci.numscore, msoc.numscore
order by st.grade_level, st.lastfirst

尝试过的代码（出现多行问题） #

Select distinct st.schoolID, st.student_number, st.lastfirst, st.grade_level, 
-- CASE WHEN ts.ID = 53 and ts.testID = 3 and stest.test_date = max(stest.test_date) THEN sts.numscore END as sci,
t4.failcount, t1.poabs, t1.popre,
round(((t1.popre - (t1.poabs + (case when t2.xtra_att>=0 then t2.xtra_att else 0 end)))/(case when t1.popre <>0 then t1.popre end))*100,1)||'%' as Attendance_Rate,
CASE WHEN round(((t1.popre - (t1.poabs + (case when t2.xtra_att>=0 then t2.xtra_att else 0 end)))/(case when t1.popre <>0 then t1.popre end))*100,1) < 90 then 'YES' else 'NO' end as ChronicAbsence,
CASE WHEN t3.discount > 0 THEN 'YES' ELSE 'NO' END as Supspended,
CASE WHEN smi.flagspeced = 1 THEN 'YES' ELSE 'NO' END as SPECED,
CASE WHEN smi.flaglep = 1 THEN 'YES' ELSE 'NO' END as ELL,
CASE WHEN st.lunchstatus in('F','R') THEN 'YES' ELSE 'NO' END as FRED
from students st
left outer join storedgrades sg on sg.studentID = st.ID
inner join S_MI_STU_GC_X smi on smi.studentsdcid = st.dcid
inner join U_def_ext_students ext on ext.studentsDCID = st.dcid
-- inner join studenttestscore sts on sts.studentID = st.ID
-- inner join testscore ts on ts.ID = sts.testscoreID 
-- inner join studenttest stest on stest.id = sts.studenttestid

优化方案 #

1. 用条件聚合合并考试分数查询，避免多表关联 #

将多个针对studenttestscore的子查询合并为一次关联，通过MAX(CASE...)提取对应考试的分数，确保每个学生仅一行数据：

SELECT 
    st.schoolID, 
    st.student_number, 
    st.lastfirst, 
    st.grade_level,
    -- 按考试类型提取分数
    MAX(CASE WHEN ts.id = 105 AND ts.testID = 1 THEN sts.numscore END) AS SAT,
    MAX(CASE WHEN ts.id = 155 AND ts.testID = 103 THEN sts.numscore END) AS PSAT,
    MAX(CASE WHEN ts.ID = 53 AND ts.testID = 3 THEN sts.numscore END) AS MStempScience,
    MAX(CASE WHEN ts.ID = 54 AND ts.testID = 3 THEN sts.numscore END) AS MStempSoc,
    t4.failcount, 
    t1.poabs, 
    t1.popre,
    -- 简化空值处理与计算
    ROUND(((t1.popre - (t1.poabs + NVL(t2.xtra_att, 0)))/NULLIF(t1.popre, 0))*100,1)||'%' AS Attendance_Rate,
    CASE WHEN ROUND(((t1.popre - (t1.poabs + NVL(t2.xtra_att, 0)))/NULLIF(t1.popre, 0))*100,1) < 90 THEN 'YES' ELSE 'NO' END AS ChronicAbsence,
    CASE WHEN NVL(t3.discount, 0) > 0 THEN 'YES' ELSE 'NO' END AS Supspended,
    CASE WHEN smi.flagspeced = 1 THEN 'YES' ELSE 'NO' END AS SPECED,
    CASE WHEN smi.flaglep = 1 THEN 'YES' ELSE 'NO' END AS ELL,
    CASE WHEN st.lunchstatus IN('F','R') THEN 'YES' ELSE 'NO' END AS FRED
FROM students st
INNER JOIN S_MI_STU_GC_X smi ON smi.studentsdcid = st.dcid
INNER JOIN U_def_ext_students ext ON ext.studentsDCID = st.dcid
INNER JOIN (
    SELECT 
        psmd.studentID, 
        SUM(periods_absent) AS poabs, 
        SUM(potential_periods_present) AS popre
    FROM ps_membership_defaults psmd
    WHERE calendardate BETWEEN '05-SEP-22' AND '09-NOV-22'          
    GROUP BY studentID
) t1 ON t1.studentID = st.ID
LEFT JOIN (      
    SELECT 
        att.studentID, 
        SUM(CASE 
            WHEN ATT_Code IN ('TDY','ETD','WTH') AND att.schoolid<25 THEN 0.14289  
            WHEN ATT_Code IN ('TDY','ETD','WTH') AND att.schoolid IN (28,38,40) THEN 0.0278 
        END) AS xtra_att
    FROM Attendance att
    INNER JOIN attendance_code attc ON att.ATTENDANCE_CODEID = attc.ID AND att.SCHOOLID = attc.SCHOOLID AND att.YEARID = attc.YEARID
    WHERE att.att_date BETWEEN '05-SEP-22' AND '09-NOV-22'
    GROUP BY att.studentID
) t2 ON t2.studentID = st.ID 
LEFT JOIN (
    SELECT 
        l.studentID,  
        SUM(CASE WHEN l.consequence IN ('SPNI', 'SPNO') THEN 1 ELSE 0 END) AS discount
    FROM log l
    WHERE l.schoolID = 28
      AND l.discipline_incidentdate BETWEEN '05-SEP-22' AND '09-NOV-22'   
    GROUP BY l.studentID
) t3 ON t3.studentID = st.ID
LEFT JOIN (
    SELECT 
        st.ID, 
        SUM(CASE WHEN pgf.grade = 'F' AND pgf.finalgradename IN ('S1', 'S2') THEN 1 ELSE 0 END) AS failcount
    FROM students st
    INNER JOIN pgfinalgrades pgf ON pgf.studentid=st.id
    WHERE st.grade_level = 12
      AND st.enroll_status = 0
      AND pgf.startdate >= '05-SEP-22'
      AND st.schoolID = 28
    GROUP BY st.ID
) t4 ON t4.ID = st.ID
-- 仅关联一次考试相关表
LEFT JOIN studenttestscore sts ON sts.studentID = st.ID
LEFT JOIN testscore ts ON ts.ID = sts.testscoreID 
LEFT JOIN studenttest stest ON stest.id = sts.studenttestid AND stest.test_date BETWEEN '01-JAN-22' AND '09-NOV-22'
WHERE st.grade_level = 12
  AND st.enroll_status = 0
  AND st.schoolid = 28
-- 过滤需要的考试类型，减少数据量
AND (ts.id IN (105,155,53,54) OR ts.id IS NULL)
GROUP BY st.schoolID, st.student_number, st.lastfirst, st.grade_level, t4.failcount, t1.poabs, t1.popre, t2.xtra_att, t3.discount, smi.flagspeced, smi.flaglep, st.lunchstatus
ORDER BY st.grade_level, st.lastfirst;

2. 用CTE缓存重复计算，提升效率 #

将重复的出勤率计算放到CTE中，避免重复执行相同逻辑：

WITH attendance_calc AS (
    SELECT 
        t1.studentID,
        t1.poabs,
        t1.popre,
        NVL(t2.xtra_att, 0) AS xtra_att,
        ROUND(((t1.popre - (t1.poabs + NVL(t2.xtra_att, 0)))/NULLIF(t1.popre, 0))*100,1) AS attendance_rate_value
    FROM (
        SELECT 
            psmd.studentID, 
            SUM(periods_absent) AS poabs, 
            SUM(potential_periods_present) AS popre
        FROM ps_membership_defaults psmd
        WHERE calendardate BETWEEN '05-SEP-22' AND '09-NOV-22'          
        GROUP BY studentID
    ) t1
    LEFT JOIN (      
        SELECT 
            att.studentID, 
            SUM(CASE 
                WHEN ATT_Code IN ('TDY','ETD','WTH') AND att.schoolid<25 THEN 0.14289  
                WHEN ATT_Code IN ('TDY','ETD','WTH') AND att.schoolid IN (28,38,40) THEN 0.0278 
            END) AS xtra_att
        FROM Attendance att
        INNER JOIN attendance_code attc ON att.ATTENDANCE_CODEID = attc.ID AND att.SCHOOLID = attc.SCHOOLID AND att.YEARID = attc.YEARID
        WHERE att.att_date BETWEEN '05-SEP-22' AND '09-NOV-22'
        GROUP BY att.studentID
    ) t2 ON t2.studentID = t1.studentID
)
SELECT 
    st.schoolID, 
    st.student_number, 
    st.lastfirst, 
    st.grade_level,
    MAX(CASE WHEN ts.id = 105 AND ts.testID = 1 THEN sts.numscore END) AS SAT,
    MAX(CASE WHEN ts.id = 155 AND ts.testID = 103 THEN sts.numscore END) AS PSAT,
    MAX(CASE WHEN ts.ID = 53 AND ts.testID = 3 THEN sts.numscore END) AS MStempScience,
    MAX(CASE WHEN ts.ID = 54 AND ts.testID = 3 THEN sts.numscore END) AS MStempSoc,
    t4.failcount, 
    ac.poabs, 
    ac.popre,
    ac.attendance_rate_value||'%' AS Attendance_Rate