1050. Actors and Directors Who Cooperated At Least Three Times

題目敘述

SQL Schema >

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')

Table: ActorDirector

+-------------+---------+ | Column Name | Type | +-------------+---------+ | actor_id | int | | director_id | int | | timestamp | int | +-------------+---------+ timestamp is the primary key column for this table.

Write a SQL query for a report that provides the pairs (actor_id, director_id) where the actor has cooperated with the director at least three times.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: ActorDirector table: +-------------+-------------+-------------+ | 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 | +-------------+-------------+-------------+ Output: +-------------+-------------+ | actor_id | director_id | +-------------+-------------+ | 1 | 1 | +-------------+-------------+ Explanation: The only pair is (1, 1) where they cooperated exactly 3 times.

題目翻譯

解法解析

解法範例

MySQL

# Write your MySQL query statement below
SELECT actor_id,
    director_id
FROM ActorDirector
GROUP BY actor_id,
    director_id
HAVING count(*) >= 3;