comments | difficulty | edit_url | tags | |
---|---|---|---|---|
true |
简单 |
|
ActorDirector
表:
+-------------+---------+ | Column Name | Type | +-------------+---------+ | actor_id | int | | director_id | int | | timestamp | int | +-------------+---------+ timestamp 是这张表的主键(具有唯一值的列).
编写解决方案找出合作过至少三次的演员和导演的 id 对 (actor_id, director_id)
示例 1:
输入: 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 次。
我们可以使用 GROUP BY
语句,按照 actor_id
和 director_id
字段进行分组,然后使用 HAVING
语句,筛选出现次数大于等于 actor_id
和 director_id
。
# Write your MySQL query statement below
SELECT actor_id, director_id
FROM ActorDirector
GROUP BY 1, 2
HAVING COUNT(1) >= 3;