mysql - MySQL:列出每学期参加6个以上(与众不同)类(class)的学生(姓名)

给定架构：

Student(Snum, Sname)
Course(Cnum, Cname)
Prerequisite(Cnum, Prereq)
Professor(Pnum,Pname, Dept, Office)
Enrollment(Snum, Cnum, Term, Section, Mark)
Schedule(Cnum, Term, Section, Days, Time, Room)
Class(Cnum, Term, Section, Instructor)

我想出了：

SELECT * FROM Student s
HAVING MIN(
    SELECT COUNT(*) FROM Enrollment e
    WHERE e.Snum = s.Snum
    GROUP BY e.Term
) > 6

但我得到：


  / * SQL错误（1064）：您有一个错误
  用你的SQL语法；查看手册
  对应于您的MySQL服务器
  使用正确语法的版本
  靠近“ SELECT COUNT（*）FROM Enrollment”
  e WHERE e.Snum = s.Snum GROUP BY
  e.Term）>'在第3行* /


关于如何解决此问题的任何想法吗？谢谢！

另外，知道我有正确的逻辑=）将令人放心

编辑：最终答案...

SELECT Student.Sname
FROM(
    SELECT COUNT(DISTINCT Cnum) as `classes`, e.Term as `term`, e.Snum as `student`
    FROM Enrollment e
    GROUP BY e.Term, e.Snum
) x
JOIN Student ON Student.Snum = `student`
WHERE x.`classes` > 6

最佳答案

如果您想查看每个学期至少上六堂课的学生：

SELECT * FROM Student s
WHERE (SELECT MIN(perTerm) FROM(
    SELECT COUNT(DISTINCT Cnum) as perTerm FROM Enrollment e
    WHERE e.Snum = s.Snum
    GROUP BY e.Term
)) > 6

干得好。您快到了，但是如果没有HAVING，则不能使用GROUP BY。

如果要使用任何术语，请用MIN替换MAX，并且如果要使用特定术语，请使用Vegard的更新版本。



如果double子查询不起作用，请尝试以下一项：

SELECT `student`
FROM(
    SELECT COUNT(DISTINCT Cnum) as `classes`, s.Term as `term`, s.Snum as `student`
    FROM Enrollment e
    GROUP BY e.Term, e.Snum
)
GROUP BY `term`
HAVING MIN(`classes`) > 6