mysql - 编写 HAVING 条件的最有效方法

Question

我目前正在完成 More JOIN operations sqlzoo 的教程，遇到了下面的代码作为#12 的答案:

SELECT yr,COUNT(title) 
FROM movie 
JOIN casting 
JOIN actor   
ON actorid=actor.id AND movie.id=movieid
WHERE name='John Travolta'
GROUP BY yr

HAVING COUNT(title)=
(SELECT MAX(c) FROM
(SELECT yr, COUNT(title) AS c 
FROM movie 
JOIN casting ON movie.id=movieid
JOIN actor   ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr) AS t
)

难道没有更简洁的方式来表达这段代码吗？

Answer 1

是的。如果我正确理解了这个问题，那就简单多了。

SELECT yr,COUNT(title) 
   FROM movie JOIN casting ON movie.id=movieid
        JOIN 
        actor   ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr
HAVING COUNT(title) > 2