Oracle SQL查询每门课程先修课数量及筛选多先修课课程

Let's break down how to solve this problem step by step. The goal is to get each course's prerequisite count and filter those with more than one, while including relevant section details from the SECTION table.

Approach #

1. Aggregate Prerequisite Counts: First, we need to calculate how many prerequisites each course has using the PREREQ table. We'll use a subquery to group courses by their ID and count their prerequisites, then filter out any courses with 1 or fewer prerequisites using the HAVING clause (since we can't use WHERE with aggregate functions like COUNT).
2. Join with Section Details: Next, we join this aggregated result with the SECTION table to pull in the section ID, course number, and academic year details you need.

Final SQL Query #

SELECT
    a.SECTION_ID AS "Section ID",
    a.COURSE_NUM AS "Course Number",
    a.SEMESTER || ' ' || a.YEAR AS "Academic Year",
    b.PREREQ_COUNT AS "Prerequisite Count"
FROM
    EB.SECTION a
JOIN (
    -- Subquery to count prerequisites per course and filter those with >1
    SELECT
        COURSE_NUM,
        COUNT(PREREQ) AS PREREQ_COUNT
    FROM
        EB.PREREQ
    GROUP BY
        COURSE_NUM
    HAVING
        COUNT(PREREQ) > 1
) b ON a.COURSE_NUM = b.COURSE_NUM;

Key Notes #

• If your PREREQ table uses a different column name for the prerequisite course (e.g., PREREQ_COURSE instead of PREREQ), just replace COUNT(PREREQ) with the correct column name.
• If there's a chance of duplicate prerequisite entries for the same course, use COUNT(DISTINCT PREREQ) instead to get an accurate count of unique prerequisites.
• The JOIN ensures we only include sections for courses that meet the prerequisite count criteria. A LEFT JOIN would include sections for courses with no prerequisites, but that's not needed here since we're filtering for courses with more than one prerequisite.

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