力扣sql中等篇练习(九)
1 电影评分
1.1 题目内容
1.1.1 基本题目信息1
1.1.2 基本题目信息2
1.1.3 示例输入输出
a 示例输入
b 示例输出
1.2 示例sql语句
SELECT t3.results
FROM
(SELECT u.name results FROM MovieRating mINNER JOIN Users uON m.user_id=u.user_idGROUP BY u.nameORDER BY count(*) desc,u.name asclimit 0,1
)t3
UNION ALL
SELECT t4.results
FROM
(SELECT m2.title resultsFROM(SELECT *FROM MovieRating WHERE date_format(created_at,"%Y-%m")='2020-02')t1INNER JOIN Movies m2ON t1.movie_id=m2.movie_idGROUP BY m2.titleORDER BY avg(t1.rating) desc,m2.title asclimit 0,1
)t4
1.3 运行截图
2 矩形面积
2.1 题目内容
2.1.1 基本题目信息
2.1.2 示例输入输出
a 示例输入
b 示例输出
2.2 示例sql语句
SELECT p1.id P1,p2.id P2,ABS(p1.x_value-p2.x_value)*ABS(p1.y_value-p2.y_value) AREA
FROM Points p1
INNER JOIN Points p2
ON p1.id<p2.id AND p1.x_value <> p2.x_value AND p1.y_value <> p2.y_value
ORDER BY AREA desc,P1 asc,P2 asc
2.3 运行截图
3 当选者
3.1 题目内容
3.1.1 基本题目信息
3.1.2 示例输入输出
3.2 示例sql语句
SELECT name
FROM
candidate
WHERE id=
(SELECT t1.candidateIdFROM(SELECT candidateId,count(*) numFROM VoteGROUP BY candidateIdORDER BY num desclimit 0,1)t1
)
3.3 运行截图