sqlite 对数据库执行SQL查询时出错：误用Aggregate：Count()

qvk1mo1f 于 2022-11-15 发布在 SQLite

关注(0)|答案(2)|浏览(212)

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个副本的所有电影片名，但我似乎找不到使用子查询的正确方法。

sqlite

来源：https://stackoverflow.com/questions/73884838/error-while-executing-sql-query-on-database-misuse-of-aggregate-count

2条答案

按热度按时间

ibps3vxo1#

您必须在表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
);

赞(0）回复(0）举报 2022-11-15

rt4zxlrg2#

这应该可以做到：

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)

赞(0）回复(0）举报 2022-11-15

我来回答

sqlite 对数据库执行SQL查询时出错：误用Aggregate：Count()

2条答案

相关问题

热门标签

最新问答