数据导入:
Create table If Not Exists ActorDirector (actor_id int, director_id int, timestamp int); Truncate table ActorDirector; insert into ActorDirector (actor_id, director_id, timestamp) values ('1', '1', '0'); insert into ActorDirector (actor_id, director_id, timestamp) values ('1', '1', '1'); insert into ActorDirector (actor_id, director_id, timestamp) values ('1', '1', '2'); insert into ActorDirector (actor_id, director_id, timestamp) values ('1', '2', '3'); insert into ActorDirector (actor_id, director_id, timestamp) values ('1', '2', '4'); insert into ActorDirector (actor_id, director_id, timestamp) values ('2', '1', '5'); insert into ActorDirector (actor_id, director_id, timestamp) values ('2', '1', '6');
ActorDirector
表:
+-------------------+----------+
| Column Name | Type |
+--------------------+---------+
| actor_id | int |
| director_id | int |
| timestamp | int |
+--------------------+---------+
timestamp 是这张表的主键.
写一条SQL查询语句获取合作过至少三次的演员和导演的 id 对 (actor_id, director_id)。
示例:
ActorDirector 表:
+-------------+---------------+----------------+
| actor_id | director_id | timestamp |
+-------------+---------------+----------------+
| 1 | 1 | 0 |
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 2 | 1 | 5 |
| 2 | 1 | 6 |
+-------------+---------------+----------------+Result 表:
+-------------+----------------+
| actor_id | director_id |
+-------------+----------------+
| 1 | 1 |
+-------------+----------------+
唯一的 id 对是 (1, 1),他们恰好合作了 3 次。
方法一
分组统计每组组合出现的次数,然后筛选出合作次数不少于3的组合
select
actor_id,
director_id
from (
select
actor_id,
director_id,
count(timestamp) as ct
from actordirector
group by actor_id, director_id
having ct >= 3
) t
;
方法二
或者可以用下面的写法,不过这种写法时间更长,我也不知道为啥
select
actor_id,
director_id
from actordirector
group by actor_id, director_id
having count(timestamp) >= 3;
来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/actors-and-directors-who-cooperated-at-least-three-times
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。