因此，我必须做一个查询，在那里我被要求找到计数的导演谁，每对电影的类型，都指导了这两个。我想我应该取两个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
2条答案
按热度按时间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
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: