sql查询pair-remove-duplicates

qyswt5oh  于 2021-06-25  发布在  Mysql
关注(0)|答案(2)|浏览(379)

因此,我必须做一个查询,在那里我被要求找到计数的导演谁,每对电影的类型,都指导了这两个。我想我应该取两个genre id和两个directors id的示例,找出g\u id不同但dir\u id相同的地方,所以我尝试了这样的方法

select distinct g1.genre_id as genre1,
            g2.genre_id as genre2,
   count(distinct mhd1.director_id) as directors_count
from genre g1, genre g2, movie_has_genre mhg1,movie_has_genre 
     mhg2,movie_has_director mhd1,movie_has_director mhd2    
where 
(g2.genre_id <> g1.genre_id) and  (mhg1.genre_id = g1.genre_id) 
and (mhg2.genre_id = g2.genre_id) and (mhd1.movie_id = mhg1.movie_id)
and  (mhd2.movie_id = mhg2.movie_id) and (mhd1.director_id =mhd2.director_id)
group by g1.genre_id, g2.genre_id;

基地是

actor(actor_id,first_name,last_name,gender)

director(director_id,first_name,last_name)

role(movie_id,actor_id,role)

genre(genre_id,name)

movie(movie_id,title,year,rank)

movie_has_director(movie_id,director_id)

movie_has_genre(genre_id,movie_id)

但它不起作用。我错过了什么?谢谢您
编辑的问题似乎是我得到(a,b)和(b,a)对,而我应该只得到(a,b)和a

44u64gxh

44u64gxh1#

再加上@paul的答案,你可以使用 DISTINCT, LEAST, GREATEST ```
select DISTINCT LEAST(g1.name,g2.name) as genre1,
GREATEST(g1.name,g2.name) as genre2,
count(distinct md1.director_id) as directors_count
from genre g1
join genre g2 on g2.id <> g1.id
join movie_has_genre mg1 on mg1.genre_id = g1.id
join movie_has_genre mg2 on mg2.genre_id = g2.id
join movie_has_director md1 on md1.movie_id = mg1.movie_id
join movie_has_director md2 on md2.movie_id = mg2.movie_id
and md2.director_id = md1.director_id
group by g1.id, g2.id

n53p2ov0

n53p2ov02#

首先,您需要一种交叉连接来获得两种类型的所有组合: genre g1 join genre g2 on g2.id > g1.id . 那你需要加入 movie_has_genre 以及 movie_has_director 对双方( g1 以及 g2 )只在导演相同的地方排成一行( md2.director_id = md1.director_id ). 剩下的是基本的 GROUP BY 以及 COUNT :

select g1.name as genre1,
       g2.name as genre2,
       count(distinct md1.director_id) as directors_count
from genre g1
join genre g2 on g2.id > g1.id
join movie_has_genre mg1 on mg1.genre_id = g1.id
join movie_has_genre mg2 on mg2.genre_id = g2.id
join movie_has_director md1 on md1.movie_id = mg1.movie_id
join movie_has_director md2 on md2.movie_id = mg2.movie_id
                           and md2.director_id = md1.director_id
group by g1.id, g2.id

相关问题