SQLite多行转列：学生与科目关联查询需求实现

Alright, let's work through this problem together. First, I’ll make some safe assumptions about your table structures since you didn’t spell them out—we’ll use standard naming that’s easy to adjust to your actual schema:

• students: student_id (primary key), student_name
• subjects: subject_id (primary key), subject_name
• student_subjects: student_id (foreign key linking to students), subject_id (foreign key linking to subjects)

Method 1: Window Functions (Best for Modern Databases) #

This is the cleanest, most efficient approach using ROW_NUMBER() to rank subjects per student, then filtering to keep only the top 2. It works in PostgreSQL, MySQL 8+, SQL Server, and most other modern SQL systems.

SELECT 
    s.student_id,
    s.student_name,
    sub.subject_name
FROM (
    SELECT 
        ss.student_id,
        ss.subject_id,
        -- Assign a unique rank to each subject for the same student
        ROW_NUMBER() OVER (
            PARTITION BY ss.student_id 
            ORDER BY sub.subject_name -- Adjust this to control which subjects get picked first
        ) AS subject_rank
    FROM student_subjects ss
    JOIN subjects sub ON ss.subject_id = sub.subject_id
) ranked_subjects
JOIN students s ON ranked_subjects.student_id = s.student_id
WHERE ranked_subjects.subject_rank <= 2; -- Keep only the first 2 subjects per student

Quick Tips: #

• The ORDER BY inside ROW_NUMBER() lets you prioritize subjects—sort by subject_id, enrollment date (if you have that column), or any other field that makes sense for your use case.
• If you want to allow ties (e.g., two subjects that should both be kept even if they’re "ranked" the same), replace ROW_NUMBER() with RANK().

Method 2: For Older Databases (No Window Functions) #

If you’re stuck with an older SQL version (like MySQL 5.x) that doesn’t support window functions, a correlated subquery will get the job done (though it’s less efficient for large datasets):

SELECT 
    s.student_id,
    s.student_name,
    sub.subject_name
FROM students s
JOIN student_subjects ss ON s.student_id = ss.student_id
JOIN subjects sub ON ss.subject_id = sub.subject_id
WHERE (
    -- Count how many subjects come before/equal to the current one for this student
    SELECT COUNT(*) 
    FROM student_subjects ss2 
    WHERE ss2.student_id = ss.student_id AND ss2.subject_id <= ss.subject_id
) <= 2; -- Adjust the condition to match your desired subject order

Including Students With No Subjects #

If you want to show every student even if they have 0 subjects (instead of only those enrolled in 1-2), use LEFT JOIN instead:

SELECT 
    s.student_id,
    s.student_name,
    sub.subject_name
FROM students s
LEFT JOIN (
    SELECT 
        ss.student_id,
        ss.subject_id,
        ROW_NUMBER() OVER (PARTITION BY ss.student_id ORDER BY sub.subject_name) AS subject_rank
    FROM student_subjects ss
    JOIN subjects sub ON ss.subject_id = sub.subject_id
) ranked_subjects ON s.student_id = ranked_subjects.student_id AND ranked_subjects.subject_rank <= 2
LEFT JOIN subjects sub ON ranked_subjects.subject_id = sub.subject_id;

This will return NULL for subject_name for students with no enrolled subjects.

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