英文:
Finding the exclusive records in a table
问题
我是SQL的初学者。我有一个名为enrolment的单个表，其中包含用户（user_id），用户可以选择单个或多个课程，这些课程使用course_id表示。
我想找出只注册了课程10的用户，没有未付费用（due_fee），也没有其他课程。这是我对这个问题的尝试，但它没有起作用：
SELECT user_id FROM student_enrollment WHERE course_id = 10 and due_fee = 0.00
这个查询选择了具有course_id为10的用户，但如何过滤掉同时具有10和其他course_id的用户呢？最终结果应该只包含course_id为10的用户。
英文:
I am a beginner at SQL. I have a single table called enrolment where I have users (user_id) who can have either single or multiple courses which are represented using course_id.
I want to find the users who are only enrolled into the course 10, have no due fees (due_fee) and nothing else.
This is my attempt at this problem but it is not working:
SELECT user_id FROM student_enrollment WHERE course_id = 10 and due_fee = 0.00
This query is selecting the users who have a course_id of 10 but how do I filter this so that the users who have both 10 and other course_id's are removed? Then the end result would only be the users with course_id 10.
答案1
得分: 2
你可以使用exists逻辑来表达这个查询：
SELECT user_id
FROM student_enrollment se1
WHERE course_id = 10 AND due_fee = 0 AND
NOT EXISTS (
SELECT 1
FROM student_enrollment se2
WHERE se2.user_id = se1.user_id AND
se2.course_id <> 10
);
另一种方法是使用聚合函数：
SELECT user_id
FROM student_enrollment
WHERE due_fee = 0
GROUP BY user_id
HAVING SUM(course_id <> 10) = 0;
英文:
You could phrase this using exists logic:
<!-- language: sql -->
SELECT user_id
FROM student_enrollment se1
WHERE course_id = 10 AND due_fee = 0 AND
NOT EXISTS (
SELECT 1
FROM student_enrollment se2
WHERE se2.user_id = se1.user_id AND
se2.course_id <> 10
);
Another approach, using aggregation:
<!-- language: sql -->
SELECT user_id
FROM student_enrollment
WHERE due_fee = 0
GROUP BY user_id
HAVING SUM(course_id <> 10) = 0;
答案2
得分: 2
一个简单的方法是使用EXCEPT：找到所有没有缴纳费用的参加课程#10的用户，但排除那些同时参加其他课程的用户。
SELECT user_id FROM student_enrollment WHERE course_id = 10 and due_fee = 0
EXCEPT
SELECT user_id FROM student_enrollment WHERE course_id <> 10
更多信息可以参考：https://dev.mysql.com/doc/refman/8.0/en/except.html
英文:
A simple way is to use EXCEPT: Find all users that take a class #10 without due fee except those who also take another class.
SELECT user_id FROM student_enrollment WHERE course_id = 10 and due_fee = 0
EXCEPT
SELECT user_id FROM student_enrollment WHERE course_id <> 10
答案3
得分: 2
使用条件聚合和筛选：
使用条件聚合和筛选的SQL查询语句如下：
方法一：
SELECT user_id
FROM
(
SELECT user_id,
count(case when course_id = 10 then 1 else 0 end) cnt_10,
count(case when course_id <> 10 then 1 else 0 end) cnt_other
FROM student_enrollment s
GROUP BY user_id
) s
WHERE cnt_10 > 0 and cnt_other = 0
方法二：
SELECT user_id
FROM student_enrollment s
GROUP BY user_id
HAVING count(case when course_id = 10 then 1 else 0 end) > 0
AND count(case when course_id <> 10 then 1 else 0 end) = 0
以上是两种使用条件聚合和筛选的SQL查询语句。
英文:
Using conditional aggregation and filter:
SELECT user_id
FROM
(
SELECT user_id,
count(case when course_id = 10 then 1 else 0 end) cnt_10,
count(case when course_id <> 10 then 1 else 0 end) cnt_other
FROM student_enrollment s
GROUP BY user_id
) s
WHERE cnt_10>0 and cnt_other=0
OR
SELECT user_id
FROM student_enrollment s
GROUP BY user_id
HAVING count(case when course_id = 10 then 1 else 0 end)>0
AND count(case when course_id <> 10 then 1 else 0 end)=0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论