如何用单条SQL查询学生当月最新正式/模拟考试成绩

Alright, let's work through this problem step by step. The goal is to pull each student's most recent monthly score, prioritizing formal exams first—only falling back to mock exams if there's no formal exam for that student-subject pair in the month. And we need to do this with a single SQL query.

Core Approach #

We can leverage window functions to rank records within each student-subject group, then pick the top-ranked entry. Here's the breakdown:

• Group by student and subject: Use PARTITION BY to isolate each name + class pair.
• Rank with priority: First sort by exam type (formal gets higher priority than mock), then by exam time (newest first).
• Select top-ranked records: Grab the first entry from each group—this will be our desired result.

SQL Query #

WITH ranked_scores AS (
    SELECT 
        name,
        class,
        mode,
        score,
        exam_time,
        ROW_NUMBER() OVER (
            PARTITION BY name, class
            ORDER BY 
                -- Prioritize formal exams over mock ones
                CASE WHEN mode = 'formal' THEN 0 ELSE 1 END,
                -- For exams of the same type, pick the most recent
                exam_time DESC
        ) AS rank_num
    FROM scores
    -- Filter to only include exams from the current month
    WHERE DATE_TRUNC('month', exam_time) = DATE_TRUNC('month', CURRENT_DATE)
)
SELECT name, class, mode, score, exam_time
FROM ranked_scores
WHERE rank_num = 1;

How It Works #

1. CTE ranked_scores: This adds a ranking column to every record.
   • The CASE statement assigns a lower number to formal exams, ensuring they appear above mock exams in the ranking.
   • For exams of the same type, we sort by exam_time in descending order so the newest exam gets the top rank (1).
2. Outer Query: We only select records where rank_num = 1—these are the highest-priority (formal if available, else mock) latest exams for each student and subject.

Adjustment for MySQL Users #

If you're using MySQL (which doesn't support DATE_TRUNC), replace the WHERE clause with this to filter for the current month:

WHERE DATE_FORMAT(exam_time, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')

Testing this query with your sample data will return exactly the expected results: Alice's Math score is the latest formal exam (88 on 2019-03-18), her Science score is the only mock exam available, and Bob's results follow the same logic.

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