mySQL使用有效语法在子查询上给出语法错误

I'm trying to find the film that has been rented the most without using limit. I'm trying to use the following query:

我试图找到最大限度租用的电影而不使用限制。我正在尝试使用以下查询:

SELECT f.title, f.film_id
FROM film f
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON r.inventory_id = i.inventory_id
GROUP BY f.film_id
HAVING COUNT(r.rental_id) = MAX(
    SELECT COUNT(r2.rental_id)
    FROM rental r2, inventory i2
    WHERE i2.inventory_id = r2.inventory_id
    GROUP BY i2.film_id);

but mySQL tells me that I have a syntax error somewhere in here SELECT COUNT(r2.rental_id) FROM rental r2, inventory however, when I run the subquery independently it returns the expected table. Am I doing something massively wrong?

但mySQL告诉我,这里有一个语法错误SELECT COUNT(r2.rental_id)FROM rental r2,库存然而,当我独立运行子查询时,它会返回预期的表。我做了大错的事吗?

relevant database schema:

相关数据库架构:

film(film  id, title, description, release year, language id, original language id, rental duration, rental rate, length, replacement cost, rating, special features, last update)
inventory(inventory id, film id, store id, last update)
rental(rental id, rental date, inventory id, customer id, return date, staff id, last update)

2 个解决方案

#1

You can't use MAX() over a result set, but you can use

您不能在结果集上使用MAX(),但您可以使用

someValue >= ALL (subquery)

to achieve what you're attempting, because ALL requires that the preceding operator be true for all values in the set.

实现您正在尝试的内容,因为ALL要求前一个运算符对集合中的所有值都为true。

Try this:

SELECT f.title, f.film_id
FROM film f
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON r.inventory_id = i.inventory_id
GROUP BY f.film_id
HAVING COUNT(r.rental_id) >= ALL (
    SELECT COUNT(r2.rental_id)
    FROM rental r2, inventory i2
    WHERE i2.inventory_id = r2.inventory_id
    GROUP BY i2.film_id);

#2

I don't have a database to test in, but this should work:

我没有要测试的数据库,但这应该有效:

Edited to LIMIT 1 instead of SELECT TOP 1 for MySQL)

编辑为LIMIT 1而不是SELECT TOP 1 for MySQL)

SELECT f.title, f.film_id
FROM film f
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON r.inventory_id = i.inventory_id
GROUP BY f.film_id
HAVING COUNT(r.rental_id) = (SELECT COUNT(r2.rental_id)
                              FROM rental r2, inventory i2
                              WHERE i2.inventory_id = r2.inventory_id
                              GROUP BY i2.film_id
                              ORDER BY COUNT(r2.rental_id) desc
                              LIMIT 1) s