对数据库执行SQL查询时出错:聚合:count()被滥用

Question

SELECT title FROM film
WHERE film_id in (
SELECT count(inventory.inventory_id) as counter
FROM inventory
WHERE film_id = film.film_id and counter = 8
)

我试图显示"电影"中的所有电影标题，这些标题在库存中正好有8个副本，但我似乎无法找到使用子查询的正确方法。

Answer 1

您必须在表inventory中按film_id分组，并为HAVING子句中可用的8个副本设置条件：

SELECT title 
FROM film
WHERE film_id in (
SELECT film_id
FROM inventory
GROUP BY film_id
HAVING COUNT(*) = 8
);

Answer 2

这应该做到：

SELECT title
FROM film
WHERE film_id in (SELECT FILM_ID
FROM (SELECT FILM_ID, COUNT(*) AS COUNTER
FROM INVENTORY
GROUP BY FILM_ID)
WHERE COUNTER = 8)