如何用Oracle 11g SQL生成学生周课程矩阵表？

Got it, let's walk through how to build that weekly class schedule matrix you need. We'll create a query that maps 12 hourly slots (8:00–20:00) as rows, Monday to Saturday as columns, and handles up to 2 courses per time slot.

Step 1: Break Down the Problem #

First, we need three key components:

• A time dimension to generate the 12 hourly slots
• A way to split courses into their respective hourly slots (since courses span multiple hours)
• A method to pivot the data into a matrix while handling multiple courses per slot

Step 2: Full SQL Query #

Here's the complete query tailored to your StudentCourses table:

WITH hour_dim AS (
    -- Generate 12 hourly slots from 8:00 to 19:00 (each slot represents X:00 to X+1:00)
    SELECT 8 + LEVEL - 1 AS hour_slot
    FROM dual
    CONNECT BY LEVEL <= 12
),
course_hours AS (
    -- Split each course into individual hourly slots, and rank courses per slot (max 2)
    SELECT 
        sc.day,
        hd.hour_slot,
        sc.courseName,
        ROW_NUMBER() OVER (PARTITION BY sc.day, hd.hour_slot ORDER BY sc.courseName) AS course_rank
    FROM StudentCourses sc
    JOIN hour_dim hd 
        ON hd.hour_slot >= sc.startHour 
        AND hd.hour_slot < sc.endHour -- Include all hours the course runs through (e.g., 9-11 covers 9 and 10)
),
schedule_matrix AS (
    -- Pivot the data into columns for each day, merging up to 2 courses per slot
    SELECT 
        hd.hour_slot,
        -- Combine first and second course for Monday (use newline to separate)
        MAX(CASE WHEN day = 1 AND course_rank = 1 THEN courseName END) || 
        CASE WHEN MAX(CASE WHEN day = 1 AND course_rank = 2 THEN courseName END) IS NOT NULL 
             THEN CHR(10) || MAX(CASE WHEN day = 1 AND course_rank = 2 THEN courseName END) 
             ELSE '' END AS Monday,
        -- Repeat for Tuesday
        MAX(CASE WHEN day = 2 AND course_rank = 1 THEN courseName END) || 
        CASE WHEN MAX(CASE WHEN day = 2 AND course_rank = 2 THEN courseName END) IS NOT NULL 
             THEN CHR(10) || MAX(CASE WHEN day = 2 AND course_rank = 2 THEN courseName END) 
             ELSE '' END AS Tuesday,
        -- Wednesday
        MAX(CASE WHEN day = 3 AND course_rank = 1 THEN courseName END) || 
        CASE WHEN MAX(CASE WHEN day = 3 AND course_rank = 2 THEN courseName END) IS NOT NULL 
             THEN CHR(10) || MAX(CASE WHEN day = 3 AND course_rank = 2 THEN courseName END) 
             ELSE '' END AS Wednesday,
        -- Thursday
        MAX(CASE WHEN day = 4 AND course_rank = 1 THEN courseName END) || 
        CASE WHEN MAX(CASE WHEN day = 4 AND course_rank = 2 THEN courseName END) IS NOT NULL 
             THEN CHR(10) || MAX(CASE WHEN day = 4 AND course_rank = 2 THEN courseName END) 
             ELSE '' END AS Thursday,
        -- Friday
        MAX(CASE WHEN day = 5 AND course_rank = 1 THEN courseName END) || 
        CASE WHEN MAX(CASE WHEN day = 5 AND course_rank = 2 THEN courseName END) IS NOT NULL 
             THEN CHR(10) || MAX(CASE WHEN day = 5 AND course_rank = 2 THEN courseName END) 
             ELSE '' END AS Friday,
        -- Saturday
        MAX(CASE WHEN day = 6 AND course_rank = 1 THEN courseName END) || 
        CASE WHEN MAX(CASE WHEN day = 6 AND course_rank = 2 THEN courseName END) IS NOT NULL 
             THEN CHR(10) || MAX(CASE WHEN day = 6 AND course_rank = 2 THEN courseName END) 
             ELSE '' END AS Saturday
    FROM hour_dim hd
    LEFT JOIN course_hours ch ON hd.hour_slot = ch.hour_slot
    GROUP BY hd.hour_slot
    ORDER BY hd.hour_slot
)
-- Format the final output with readable time slots
SELECT 
    TO_CHAR(hour_slot) || ':00–' || TO_CHAR(hour_slot + 1) || ':00' AS time_slot,
    NVL(Monday, '') AS Monday,
    NVL(Tuesday, '') AS Tuesday,
    NVL(Wednesday, '') AS Wednesday,
    NVL(Thursday, '') AS Thursday,
    NVL(Friday, '') AS Friday,
    NVL(Saturday, '') AS Saturday
FROM schedule_matrix;

Step 3: How It Works #

Let's break down each CTE (Common Table Expression):

1. hour_dim: Uses Oracle's CONNECT BY to generate 12 hourly values (8 to 19), each representing the start of an hour-long slot (e.g., 8 = 8:00–9:00).
2. course_hours: Joins the course data with the time dimension to split each course into every hour it occupies. The ROW_NUMBER() function ranks courses in the same time slot so we can limit to the top 2.
3. schedule_matrix: Uses conditional aggregation to pivot the daily course data into columns. We combine the top 2 courses per slot with a newline (CHR(10)) for readability.
4. The final query formats the time slots into a user-friendly string and replaces NULL values with empty strings for clean output.

Example Output #

For your sample data, the Thursday 14:00–15:00 slot will show:

Case Studies in Advertising
Advertising Photography

Since both courses overlap in that hour.

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