题目:
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 次。
题解:
方法一:分组并统计次数大于3的,由于执行顺序为from,group by 执行到这里以后得到的表其实和原表一样,有人就会想那为什么不直接使用where,但是where后面不能跟count聚合函数,所以使用having。
select
actor_id, director_id
from
ActorDirector
group by
actor_id, director_id
having
count(actor_id) >= 3