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 次。
解题思路
相关子查询
本来想不用相关子查询写,用自联结或者其他的方式写,但最后还是绕回到相关子查询了。
# Write your MySQL query statement below
SELECT DISTINCT A.actor_id,A.director_id
FROM ActorDirector AS A
WHERE EXISTS(
SELECT B.actor_id,B.director_id
FROM ActorDirector AS B
WHERE A.actor_id=B.actor_id AND A.director_id=B.director_id
HAVING COUNT(B.actor_id)>=3 OR COUNT(B.director_id)>=3
)