MySQL多表查询：传入课程ID数组与学院ID生成结果表

Got it, let's break down how to build the exact query you need, based on your table structure and the requirement to pass a course ID array + college ID (like ID=8).

First, Let's Map the Table Relationships #

Quick recap to make sure we're aligned:

• courses: Stores core course info (we'll grab course_name from here)
• college_courses: Tracks how many of each course a college makes available (num_of_courses is the total accessible count for the college)
• student_courses: Logs which courses have been assigned to students for each college

The Final Query #

Here's a robust query that delivers the three pieces of data you need:

SELECT
    c.course_name,
    IFNULL(cc.num_of_courses, 0) AS total_accessible_courses,
    IFNULL(sc.student_assigned_count, 0) AS student_assigned_count
FROM
    courses c
LEFT JOIN
    college_courses cc ON c.id = cc.course_id AND cc.college_id = 8 -- Swap 8 with your input college ID
LEFT JOIN (
    -- Pre-aggregate student assignment counts to keep the query efficient
    SELECT
        course_id,
        college_id,
        COUNT(*) AS student_assigned_count
    FROM
        student_courses
    WHERE
        college_id = 8 -- Match the same college ID here
    GROUP BY
        course_id, college_id
) sc ON c.id = sc.course_id AND sc.college_id = 8
WHERE
    c.id IN (1, 3, 5) -- Replace with your course ID array
ORDER BY
    c.course_name;

Key Details & Explanations #

• Left Joins: We use LEFT JOIN instead of INNER JOIN to ensure you still see results for courses that might not have an entry in college_courses (shows 0 for total accessible) or no student assignments yet (shows 0 for assigned count). No missing rows just because a course isn't set up for the college yet.
• IFNULL Handling: Converts NULL values (from missing entries) to 0, so your result set has clean, consistent numbers instead of messy nulls.
• Subquery for Student Counts: Pre-aggregating the student assignments in a subquery is more efficient than counting on the fly in the main select clause, especially if your student_courses table is large.
• Parameterization Note: In production, replace the hardcoded 8 and (1,3,5) with prepared statement parameters (like ? in most programming languages) to avoid SQL injection and make the query reusable for different inputs.

Edge Cases to Keep in Mind #

• If a course in your input array isn't linked to the college in college_courses, total_accessible_courses will show 0.
• If no students have been assigned a specific course for the college, student_assigned_count will show 0.
• If a course ID in your array doesn't exist in the courses table, it won't appear in results (you could adjust to include these with a RIGHT JOIN if needed, but that's rarely desired).

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