mysql - MySQL 中的关联嵌套查询

Question

架构:

Student (snum: integer, 
         sname: char(30), 
         major: char(25), 
         level: char(2), 
         age: integer)

Faculty (fid: integer, 
         fname: char(30), 
         deptid: integer)

Class (cname: char(40), 
       meets_at: char(20),
       room: char(10), 
       fid: integer | fid    REFS Faculty.fid)

Enrolled (snum: integer, 
          cname: char(40) | snum REFS student.snum, 
          cname REFS class.name)

问题:

P1。查找教过最多学生的教师姓名、部门不同的类别。

P2。查找注册人数大于 5 的所有类(class)的名称及其注册人数。

我的尝试:

#P1:
select distinct f.fname,max(distinct c.cname)
from faculty f,class c
where Exists (select c.fid,max(distinct c.cname) as myCount
from class c where
f.fid=c.fid);

#P2:
select distinct c.cname
from class c
where Exists (select c.cname
from enrolled e where
e.cname=c.cname and count(e.cname)>5);

但这给了我错误。请帮帮我。

Answer 1

问题#1:只需执行group by

select f.fname, COUNT(distinct c.cname)
from faculty f 
join class c
  on f.fid = c.fid
group by f.fname
order by COUNT(distinct c.cname) DESC
limit 1

使用关联查询:

关联 v1:

SELECT *
FROM faculty f
WHERE f.id =  (SELECT c.fid
               FROM class c 
               GROUP BY c.fid
               ORDER BY COUNT(distinct c.cname) DESC
               LIMIT 1)

Corraleted v2:

SELECT *
FROM (
      SELECT f.*, (SELECT COUNT(distinct c.cname)
                   FROM class c
                   WHERE c.fid = f.id) as total
      FROM faculty f
     ) T
ORDER By Total DESC
LIMIT 1