PostgreSQL技术问询：查询所属课程研究生人数多于本科生的学生姓名

Hey there, let's work through the issues in your PostgreSQL query step by step, then fix it up to meet your requirement.

First, let's break down what's wrong with the original query:

• Incorrect use of count(): The expression count(s.type='undergrad') doesn't actually count the number of undergrad students. In PostgreSQL, s.type='undergrad' returns a boolean (true or false), and count() counts all non-null values—so both true and false get counted, meaning you're just getting the total number of students in the group, not the count of undergrads. To count specific types, you need to use either count(*) FILTER (WHERE s.type = 'undergrad') or SUM(CASE WHEN s.type = 'undergrad' THEN 1 ELSE 0 END).
• Reversed condition in HAVING: Your requirement is to find courses where postgrad students outnumber undergrads, but your original query checks for undergrads being more than postgrads. That's exactly the opposite of what you need!
• Potential join logic issues: Looking at your table names and join conditions, the relationships seem a bit off. Typically, you'd expect a Student table to link to both Person (for student details) and Course (for enrolled courses), rather than Person linking directly to Course and Course linking to Student. If your schema is actually structured that way, you can adjust, but double-check your foreign key relationships to avoid pulling incorrect data.

Now here's the corrected query, assuming a more standard schema where Student connects Person and Course:

SELECT p.name
FROM Person p
JOIN Student s ON p.id = s.person_id  -- Link student to their personal info
JOIN Course c ON s.course_id = c.id   -- Link student to their enrolled course
GROUP BY c.id, p.name  -- Group by course to check student type counts per course
HAVING COUNT(*) FILTER (WHERE s.type = 'postgrad') > COUNT(*) FILTER (WHERE s.type = 'undergrad');

If your original schema is indeed Person.course → Course.id and Course.st_info → Student.id, adjust the joins like this:

SELECT p.name
FROM Person p
JOIN Course c ON p.course = c.id
JOIN Student s ON c.st_info = s.id
GROUP BY c.id, p.name
HAVING COUNT(*) FILTER (WHERE s.type = 'postgrad') > COUNT(*) FILTER (WHERE s.type = 'undergrad');

A quick breakdown of the fixed query:

1. We join the tables correctly to get each student's name, their course, and their student type.
2. We group by course (and student name, to ensure each student's name is returned once per course they're in).
3. The HAVING clause now correctly checks if the number of postgrads in the course is greater than undergrads, filtering only those courses that meet your requirement.

内容的提问来源于stack exchange，提问作者J. Doe