如何将查询结果限制为包括一列中一定数量的项目的结果?
我正在尝试查询电影数据库的结果,这些结果返回至少有3个演员的电影。我只需要获取titleID
,就可以从那里去。如何从表actors
中拉出titleID
,其中personid
s&gt的数字; 3。
我尝试过:
SELECT titleid FROM actors WHERE COUNT(personid) > 3;
但这似乎是对count()的滥用。我可以使用一个函数吗?
模式:
CREATE TABLE actors(
titleid TEXT,
ordering INT,
personid TEXT,
category TEXT,
job TEXT,
characters TEXT
);
CREATE TABLE actornames(
personid TEXT,
name TEXT,
birth INT,
death INT,
profession TEXT,
knowntitles TEXT,
titleid TEXT,
ordering INT,
"personid:1" TEXT,
category TEXT,
job TEXT,
characters TEXT
);
CREATE TABLE ratings (titleid TEXT, rating DEC, votes INT);
CREATE TABLE movielist(title TEXT,titleid TEXT);```
I am trying to query results from my movie database that returns movies that have at least 3 actors listed. I just need to get the titleid
and I can go from there. How do I pull the titleid
from the table actors
where the number of personid
s > 3.
I have tried:
SELECT titleid FROM actors WHERE COUNT(personid) > 3;
But this appears to be a misuse of COUNT(). Is there a function I can use?
SCHEMA:
CREATE TABLE actors(
titleid TEXT,
ordering INT,
personid TEXT,
category TEXT,
job TEXT,
characters TEXT
);
CREATE TABLE actornames(
personid TEXT,
name TEXT,
birth INT,
death INT,
profession TEXT,
knowntitles TEXT,
titleid TEXT,
ordering INT,
"personid:1" TEXT,
category TEXT,
job TEXT,
characters TEXT
);
CREATE TABLE ratings (titleid TEXT, rating DEC, votes INT);
CREATE TABLE movielist(title TEXT,titleid TEXT);```
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您可以在中使用在 contregation查询的子句中使用
count()
汇总函数:以防万一可以在
Actors 对于同一部电影,您应该计算独特的
themid
s:You can use
COUNT()
aggregate function in theHAVING
clause of an aggregation query:Just in case an actor may be listed more than once in
actors
for the same movie, you should count the distinctpersonid
s: