mysql - 仅显示等于临时表中最大值的实例

Question

我正在尝试创建一个新表，其中只有 sname 等于 num_courses 的最大数量，这是临时表 中的一列n.

SELECT s.sname, n.num_courses
FROM (SELECT e.sid, COUNT(distinct e.cno) as num_courses
      FROM enroll e
      GROUP BY e.sid) n, student s
WHERE s.sid = n.sid AND n.num_courses = (SELECT MAX(n.num_course) from n) x;

是否可以只显示等于临时表中找到的最大值的实例？ (引用最后一行的第二个WHERE子句)

这是错误:

ERROR 1064 (42000) at line 1 in file: 'q7.sql': You have an error in your
SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near 'x' at line 5

它说错误在第 1 行，但是当我删除最后一行的最后一个子句时，没有错误。

Answer 1

你不能像那样重用子查询。您需要在 where 子句中再次编写查询。

我认为你打算这样做:

select s.sname,
    n.num_courses
from (
    select e.sid,
        COUNT(distinct e.cno) as num_courses
    from enroll e
    group by e.sid
    ) n,
    student s
where s.sid = n.sid
    and n.num_courses = (
        select MAX(n.num_course)
        from (
            select COUNT(distinct e.cno) as num_courses
            from enroll e
            group by e.sid
            ) t
        );