Oracle SQL如何获取ACT科目最值分数及对应考试日期？

Got it, let's tackle this! Your existing query does a great job pulling the core min/max scores, but to tie each score to its specific test date, we need to adjust our approach—since aggregate functions like MIN()/MAX() only return the score value, not the associated row data (like test_date from the studenttest table).

Instead, we can use window functions to rank each student's scores per ACT subject, then pick the top-ranked row that matches our min/max criteria. Here's the modified query that includes both scores and their corresponding dates:

WITH ranked_scores AS (
    SELECT 
        s.lastfirst,
        s.state_studentnumber,
        s.grade_level,
        s.schoolid,
        ts.name AS test_subject,
        sts.numscore,
        st.test_date,
        -- Rank scores to identify the lowest per subject
        ROW_NUMBER() OVER (
            PARTITION BY s.id, ts.name 
            ORDER BY sts.numscore ASC
        ) AS rn_min, -- 1 = lowest score for this student/subject
        -- Rank scores to identify the highest composite score
        ROW_NUMBER() OVER (
            PARTITION BY s.id, ts.name 
            ORDER BY sts.numscore DESC
        ) AS rn_max -- 1 = highest score for this student/subject
    FROM studenttestscore sts
    INNER JOIN students s ON sts.studentid = s.id
    INNER JOIN studenttest st ON sts.studenttestid = st.id
    INNER JOIN testscore ts ON sts.testscoreid = ts.id
    INNER JOIN test t ON ts.testid = t.id
    WHERE 
        t.name = 'ACT' 
        AND s.enroll_status = 0 
        AND s.schoolid = 32
)
SELECT 
    lastfirst,
    state_studentnumber,
    grade_level,
    schoolid,
    -- Pull lowest scores and their matching test dates
    MAX(CASE WHEN test_subject = 'ACT_English' AND rn_min = 1 THEN numscore END) AS ACT_English_Min,
    MAX(CASE WHEN test_subject = 'ACT_English' AND rn_min = 1 THEN test_date END) AS ACT_English_Min_Date,
    MAX(CASE WHEN test_subject = 'ACT_Reading' AND rn_min = 1 THEN numscore END) AS ACT_Reading_Min,
    MAX(CASE WHEN test_subject = 'ACT_Reading' AND rn_min = 1 THEN test_date END) AS ACT_Reading_Min_Date,
    MAX(CASE WHEN test_subject = 'ACT_Math' AND rn_min = 1 THEN numscore END) AS ACT_Math_Min,
    MAX(CASE WHEN test_subject = 'ACT_Math' AND rn_min = 1 THEN test_date END) AS ACT_Math_Min_Date,
    MAX(CASE WHEN test_subject = 'ACT_Science' AND rn_min = 1 THEN numscore END) AS ACT_Science_Min,
    MAX(CASE WHEN test_subject = 'ACT_Science' AND rn_min = 1 THEN test_date END) AS ACT_Science_Min_Date,
    -- Pull highest composite score and its test date
    MAX(CASE WHEN test_subject = 'ACT_Composite' AND rn_max = 1 THEN numscore END) AS ACT_Composite_Max,
    MAX(CASE WHEN test_subject = 'ACT_Composite' AND rn_max = 1 THEN test_date END) AS ACT_Composite_Max_Date
FROM ranked_scores
GROUP BY 
    lastfirst, 
    state_studentnumber, 
    grade_level, 
    schoolid
ORDER BY lastfirst;

Quick Breakdown of How This Works: #

• CTE ranked_scores: This subquery adds two rankings for each student's scores:
  • rn_min: Sorts scores from lowest to highest, so rn_min = 1 marks the lowest score for that student/subject.
  • rn_max: Sorts scores from highest to lowest, so rn_max = 1 marks the highest composite score for the student.
• Pivoting Results: We use CASE statements with MAX() to turn the ranked rows into columns, pairing each min/max score directly with its corresponding test_date.
• Handling Ties: If a student has multiple tests with the same min/max score, ROW_NUMBER() will pick one arbitrarily. If you want to retain all tied dates, replace ROW_NUMBER() with RANK()—just note that this might return multiple rows per student if there are ties.

内容的提问来源于stack exchange，提问作者Ginger Mckenzie