如何在我的count中获取fullfil语句的值？

noj0wjuj 于 2021-06-23 发布在 Mysql

关注(0)|答案(1)|浏览(332)

我试着展示每一对演员，两个演员没有在任何一个共同的电影类型上表演过，同时一个演员和另一个演员一起表演过的类型至少有7个
我做到了：

select a1.actor_id as i8opoios1,a2.actor_id as i8opoios2,((count(distinct(g1.genre_name))+count(distinct(g2.genre_name)))>=7) as result from actor as a1
inner join actor as a2 on a1.actor_id!=a2.actor_id

inner join role as r1 on a1.actor_id=r1.actor_id
inner join movie as m1 on m1.movie_id=r1.movie_id
inner join movie_has_genre as mg1 on mg1.movie_id=m1.movie_id
inner join genre as g1 on mg1.genre_id=g1.genre_id

inner join role as r2 on a2.actor_id=r2.actor_id
inner join movie as m2 on m2.movie_id=r2.movie_id
inner join movie_has_genre as mg2 on mg2.movie_id=m2.movie_id
inner join genre as g2 on mg2.genre_id=g2.genre_id

where a1.actor_id<a2.actor_id and mg1.genre_id!=mg2.genre_id 
group by a1.actor_id,a2.actor_id;

这个查询将返回所有没有在任何常见电影类型上播放过的演员对，如果他们在7个或更多类型上播放过，则返回1（真），如果没有，则返回0（假）。我的问题是，是否有人知道我如何只返回真的语句。

Tables and their columns:

actor(actor_id,name)
role(actor_id,movie_id)
movie(movie_id,title)
movie_has_genre(movie_id,genre_id)
genre(genre_id,gender_name)

mysql

来源：https://stackoverflow.com/questions/50570400/how-to-get-the-values-that-fullfil-the-statement-in-my-count

1条答案

按热度按时间

ryevplcw1#

将条件添加到where子句以限制行。

SELECT 
    a1.actor_id as i8opoios1,
    a2.actor_id as i8opoios2,
    IF((count(distinct(g1.genre_name))+count(distinct(g2.genre_name)))>=7,1,0) as result 
FROM actor as a1
INNER JOIN actor as a2 
    on a1.actor_id != a2.actor_id
INNER JOIN role as r1 
    on a1.actor_id = r1.actor_id
INNER JOIN movie as m1 
    on m1.movie_id = r1.movie_id
INNER JOIN movie_has_genre as mg1 
    on mg1.movie_id = m1.movie_id
INNER JOIN genre as g1 
    on mg1.genre_id = g1.genre_id
INNER JOIN role as r2 
    on a2.actor_id = r2.actor_id
INNER JOIN movie as m2 
    on m2.movie_id = r2.movie_id
INNER JOIN movie_has_genre as mg2 
    on mg2.movie_id = m2.movie_id
INNER JOIN genre as g2 
    on mg2.genre_id = g2.genre_id
WHERE a1.actor_id < a2.actor_id 
    AND mg1.genre_id != mg2.genre_id 
HAVING IF((count(distinct(g1.genre_name))+count(distinct(g2.genre_name)))>=7,1,0) = 1
GROUP BY a1.actor_id,a2.actor_id;

赞(0）回复(0）举报 2021-06-23

我来回答

如何在我的count中获取fullfil语句的值？

1条答案

相关问题

热门标签

最新问答