SQL: INNER JOIN + NOT EXIST

Question

我将尝试创建一个SQL语句，需要联接三个表。

EnrollStudents

EnrollID     UserID     SubjID
1            1          1
2            1          2
3            1          3
4            3          1
5            7          2

学生们

StudentID     UserID     YearID
1             1          1
2             3          1
3             7          1

主题

SubjID     SubjCode     YearID
1          English      1
2          Math         1
3          Science      1

输出应该是...

UserID
2
3

由于User 1已经报名了所有科目，而User 3和User 7仍然显示，因为一些课程还没有报名。

我尝试了以下SQL语句，但没有成功：

SELECT Students.UserID 
FROM Students 
WHERE Students.YearID = 1 
    AND Students.UserID NOT IN (SELECT EnrollStudents.UserID 
                                FROM EnrollStudents)

有任何想法吗？

- abramlimpin

---

做得好，表格格式化得很漂亮，sqlfiddle导入它们非常顺利。 - lc.

Answer 1

SELECT s.UserID
FROM Students AS s
LEFT OUTER JOIN EnrollStudents AS es ON s.UserID = es.UserID
GROUP BY s.UserID
HAVING COUNT(DISTINCT(es.SubjID)) < (SELECT COUNT(*) FROM Subjects)

等一下。我认为你在示例输出和EnrollStudents表中混淆了你的“StudentID”和“UserID”。

http://sqlfiddle.com/#!3/61618/1

- lc.

---

如果学生没有至少注册一门课程，这将无法运行。 - Madman

@eibhrum 这不是我的观点。在“学生”表中，您有用户ID（1,3,7），但在“注册学生”中，您正在引用用户ID（1,2,3）。我认为您的意思是StudentID。 - lc.

@Ic：更新了。抱歉搞混了。 - abramlimpin

@lc. 我能按照科目ID进行排序吗？ - abramlimpin

@eibhrum 我不确定你的意思，因为这样会失去分组。这个查询显示了哪些用户还有科目可以报名。然而，你可以将其作为子查询进行连接，以显示未报名的科目，例如... - lc.

Answer 2

看起来你正在尝试对所有第一年没有注册所有第一年必修课程的学生进行资格认证，因此只想获取学生2和3。你的数据将所有人都归为一个年份ID组，但我怀疑你实际上拥有跨越多个年份的数据，并且你只关心那些在第一年的学生以及与第一年要求相关联的科目。

第一个查询(YrSubjects)结果将预先聚合单个年份中的课程数量，因此不必为每个学生重复执行此操作。只需一次...将其作为未分配的JOIN加入到其余查询中将是笛卡尔积，但每个人只有一条记录，无论如何都不会有重复。

接下来是其余的表/连接。获取已注册仅与第一年相关联的科目的学生。WHERE子句明确限制为仅限“第一年”学生。

最终的HAVING子句应用了少于第一年要求总科目数的注册计数。使用此查询，您不会“固定”在某个硬编码的科目数量上，而是可以灵活适应变化...

SELECT 
      S.StudentID 
   FROM 
      ( select count(*) as YrClasses 
           from 
              Subjects 
           where YearID = 1 ) YrSubjects,
      Students S
         JOIN EnrollStudents ES
            on S.UserID = ES.UserID
            JOIN Subjects S
               ON ES.SubjID = S.SubjID
              AND S.YearID = 1
   WHERE 
      S.YearID = 1 
   HAVING
      count(*) < YrSubjects.YrClasses

- DRapp

Answer 3

select s.UserID
from Students s
left outer join (
    select UserID
    from EnrollStudents
    group by UserID
    having count(distinct SubjID) = 3
) a on s.UserID = a.UserID
where a.UserID is null 
    and s.YearID = 1

SQL Fiddle 示例

- D'Arcy Rittich