该题目来源于力扣:
1050. 合作过至少三次的演员和导演 - 力扣(LeetCode)
题目要求:
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分组,最后用having进行分组后条件筛选即可
代码实现:
SELECT actor_id, director_id
FROM ActorDirector
GROUP BY actor_id, director_id
HAVING COUNT(*) = 3;