1 题目描述
ActorDirector 表:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| actor_id | int |
| director_id | int |
| timestamp | int |
+-------------+---------+
timestamp 是这张表的主键 (具有唯一值的列)
编写解决方案找出合作过至少三次的演员和导演的 id 对 (actor_id, director_id)
2 测试用例
输入:
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 |
+-------------+-------------+-------------+
输出:
+-------------+-------------+
| actor_id | director_id |
+-------------+-------------+
| 1 | 1 |
+-------------+-------------+
解释:
唯一的 id 对是 (1, 1), 他们恰好合作了 3 次.
3 解题思路
- 对
actor_id, director_id进行分组统计, 使用having count(*) >= 3查找符合要求的数据
select actor_id,director_id from ActorDirector group by actor_id, director_id having count(*) >= 3
查询结果
+--------+-----------+
|actor_id|director_id|
+--------+-----------+
|1 |1 |
+--------+-----------+
