带有count（）条件和关系代数的SQL内连接(SQL inner join with count() condition, and relationnal algebra)

带有count（）条件和关系代数的SQL内连接(SQL inner join with count() condition, and relationnal algebra)

我有这些表：

电影（ id ， name ）

演员（ idmovie ， actor ）

而且我想计算每部电影的演员数量，然后只拍摄超过10位演员的电影。 我有一个查询来计算每部电影的演员数量，如下所示：

SELECT idmovie, count(actor) FROM Cast GROUP BY idmovie HAVING count(actor) > 10;

现在，我想知道如何获得该结果并将其加入到Movies表中。 我试过了：

SELECT name FROM Movies INNER JOIN (SELECT idmovie FROM Cast GROUP BY idmovie HAVING count(actor) >2) Cast ON Cast.idmovie = Movies.id;

但它不起作用。

我还必须把它翻译成关系代数。

π name (σ (count(σ id = idmovie))) Movies⨝Cast

这显然是错的......

有帮助吗？

I have these tables:

Movies (id, name)

Cast (idmovie, actor)

And I would like to count the number of actors for each movie and then only get movies with more than 10 actors. I have a query to count the number of actors for each movie, which goes like this:

SELECT idmovie, count(actor) FROM Cast GROUP BY idmovie HAVING count(actor) > 10;

Now, I wonder how to get that result and join it to the Movies table. I tried:

SELECT name FROM Movies INNER JOIN (SELECT idmovie FROM Cast GROUP BY idmovie HAVING count(actor) >2) Cast ON Cast.idmovie = Movies.id;

But it doesn't work.

I also have to translate it to relational algebra.

π name (σ (count(σ id = idmovie))) Movies⨝Cast

Which is obviously wrong...

Any help?

最满意答案

尝试这个...

SELECT m.name, COUNT(c.actor) AS 'ActorsCount' FROM Movies m INNER JOIN [Cast] c ON m.id = c.idmovie GROUP BY m.name HAVING COUNT(c.actor) > 10;

Try this...

SELECT m.name, COUNT(c.actor) AS 'ActorsCount' FROM Movies m INNER JOIN [Cast] c ON m.id = c.idmovie GROUP BY m.name HAVING COUNT(c.actor) > 10;