# Write your MySQL query statement below
1050. 合作过至少三次的演员和导演
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 次。
原站题解
pythondata 解法, 执行用时: 308 ms, 内存消耗: 60.2 MB, 提交时间: 2023-08-09 17:25:37
import pandas as pd
def actors_and_directors(actor_director: pd.DataFrame) -> pd.DataFrame:
# 使用 groupby 和 count 函数对 actor_id 和 director_id 进行分组计数
counts = actor_director.groupby(['actor_id', 'director_id']).size().reset_index(name='count')
# 从计数结果中筛选出合作次数大于等于3次的组合
result = counts[counts['count'] >= 3][['actor_id', 'director_id']]
return result
mysql 解法, 执行用时: 393 ms, 内存消耗: 0 B, 提交时间: 2022-05-27 11:19:51
# Write your MySQL query statement below select actor_id, director_id from actordirector group by actor_id,director_id having count(timestamp)>2;