2021-05-05

合作过至少三次的演员和导演

需求：编写一个 SQL 查询，查询语句获取合作过至少三次的演员和导演的 id 对 (actor_id, director_id)

展示效果：

actor_id	director_id
1	1

Create table If Not Exists 42_ActorDirector (actor_id int, director_id int, timestamp int);
Truncate table 42_ActorDirector;
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (1, 1, 0);
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (1, 1, 1);
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (1, 1, 2);
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (1, 2, 3);
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (1, 2, 4);
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (2, 1, 5);
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (2, 1, 6);

最终SQL:

select 
      actor_id,
      director_id
from 
      42_ActorDirector 
group by 
      actor_id,director_id 
having 
      count(*)>=3;