网站建设入门教程pdf,重庆网站建设科技公司,建站公司网站论坛,网页设计工作室赚钱吗大家好#xff0c;我是空空star#xff0c;本篇带大家了解一道简单的力扣sql练习题。 文章目录前言一、题目#xff1a;1050. 合作过至少三次的演员和导演二、解题1.正确示范①提交SQL运行结果2.正确示范②提交SQL运行结果3.正确示范③提交SQL运行结果4.正确示范④提交SQL运… 大家好我是空空star本篇带大家了解一道简单的力扣sql练习题。 文章目录前言一、题目1050. 合作过至少三次的演员和导演二、解题1.正确示范①提交SQL运行结果2.正确示范②提交SQL运行结果3.正确示范③提交SQL运行结果4.正确示范④提交SQL运行结果5.其他总结前言 一、题目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 次。
二、解题
1.正确示范①
提交SQL
select actor_id,director_id
from ActorDirector
group by actor_id,director_id
having count(1)3;运行结果 2.正确示范②
提交SQL
select actor_id,director_id
from ActorDirector
group by actor_id,director_id
having count(*)3;运行结果 3.正确示范③
提交SQL
select actor_id,director_id
from ActorDirector
group by actor_id,director_id
having count(timestamp)3;运行结果 4.正确示范④
提交SQL
select actor_id,director_id from(select actor_id,director_id,count(1) numfrom ActorDirector group by actor_id,director_id
) u
where num3;运行结果 5.其他 总结 正确示范①思路 按照演员和导演id分组group by actor_id,director_id 筛选合作次数大于等于3次的having count(1)3 正确示范②思路 按照演员和导演id分组group by actor_id,director_id 筛选合作次数大于等于3次的having count(*)3 正确示范③思路 按照演员和导演id分组group by actor_id,director_id 筛选合作次数大于等于3次的having count(timestamp)3 正确示范④思路 按照演员和导演id分组group by actor_id,director_id 计算出次数count(1) as num 然后在最外层限定num3。
