SQL 选择查询计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9924132/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL SELECT QUERY COUNT
提问by user1300580
I'm new to this so bear with me.
我是新手，所以请耐心等待。
I'm trying to create a select query from a Moviedatabase. Amongst other tables there is a Roletable that features information such as roleID, roleName, gender, actorID, movieID. An actor can have many roles in different movies.
我正在尝试从Movie数据库创建一个选择查询。在其他表格中，有一个Role表格包含诸如roleID, roleName, gender, actorID, movieID. 一个演员可以在不同的电影中扮演多个角色。
I am trying to create the query so it will tell me how many actors have three or more roles in the database.
我正在尝试创建查询，以便它告诉我有多少演员在数据库中拥有三个或更多角色。
I've tried a few solutions and they output data just not sure if it is right.
我尝试了一些解决方案，但它们输出的数据不确定是否正确。
SELECT COUNT (DISTINCT actorID) FROM Role WHERE actorID >= 3
SELECT COUNT (actorID) FROM Role GROUP BY movieID HAVING COUNT (actorID) >=3
回答by kaj
Try something like:
尝试类似：
select actorID, count(*)
from Roles
group by actorID
having count (*) >= 3
If you want to retrieve other properties about the actor you add those to both the select and group by clauses
如果您想检索有关演员的其他属性，请将这些属性添加到 select 和 group by 子句中
回答by ipr101
Try:
尝试：
SELECT COUNT(*) FROM Role
GROUP BY actorid
HAVING COUNT(*) >= 3
回答by Chetan
Try this ..
尝试这个 ..
SELECT COUNT(*)
FROM (SELECT actorID FROM Roles GROUP BY actorID HAVING COUNT(*)>=3) AS actor;
This query return number of actors who have three or more roles in the database.
此查询返回在数据库中具有三个或更多角色的演员的数量。
回答by Chetter Hummin
Please try the following
请尝试以下操作
SELECT actorID, COUNT(actorID)
FROM Role
GROUP BY actorID
HAVING COUNT (actorID) >=3
回答by mkk
your second query is almost correct.
您的第二个查询几乎是正确的。
You need to group by actorId, because you want to count them. You have put movieId, which makes no sense in that case.
您需要按actorId 分组，因为您想对它们进行计数。您已经输入了电影 ID，在这种情况下这没有任何意义。
SELECT COUNT (*) as actorRoles
FROM Role
GROUP BY actorId
HAVING actorRoles >=3;
回答by Apurv Gupta
select count(*)
from Role
where actorID in
(
select actorID
from Role
group by actorID
having count(actorID) > 3
)
