Leetcode SQL(四）

最新推荐文章于 2024-05-23 18:05:50 发布

一杯敬朝阳一杯敬月光

最新推荐文章于 2024-05-23 18:05:50 发布

阅读量452

点赞数

分类专栏： leetcode 题文章标签： Leetocde SQL

本文链接：https://blog.csdn.net/qq_xuanshuang/article/details/106865136

版权

leetcode 题专栏收录该内容

51 篇文章 2 订阅

订阅专栏

602. 好友申请 II ：谁有最多的好友🔒

612. 平面上的最近距离🔒

https://leetcode-cn.com/problems/shortest-distance-in-a-plane/

表 point_2d 保存了所有点（多于 2 个点）的坐标 (x,y) ，这些点在平面上两两不重合。写一个查询语句找到两点之间的最近距离，保留 2 位小数。

最近距离在点 (-1,-1) 和(-1,2) 之间，距离为 1.00 。所以输出应该为：

注意：任意点之间的最远距离小于 10000 。

题解

一：两张表join，笛卡尔积，会产生 n^ 2 个两两组合对，同时没法排除自身和自身的组合。不过好在题中交代这些点在平面上两两不重合，所以两两组合x和y不同时相等可以排除自身组合。这边用row_number函数给每一条记录生成一个id，join时用a的id join比b的id大的，可排除自己join，就算有重叠点也不影响，因为重叠点id毕竟不同，而且只有 (n^2 - n) / 2 个两两组合对。

select a.*, b.*
from (select row_number() over() as id, x, y from point_2d) a 
join (select row_number() over() as id, x, y from point_2d) b
on a.id > b.id

select round(min(sqrt(POW(a.x - b.x, 2) + POW(a.y - b.y, 2))), 2) as shortest
from (select row_number() over() as id, x, y from point_2d) a 
join (select row_number() over() as id, x, y from point_2d) b
on a.id > b.id

1355. 活动参与者🔒

https://leetcode-cn.com/problems/activity-participants/

写一条 SQL 查询那些既没有最多，也没有最少参与者的活动的名字可以以任何顺序返回结果，Activities 表的每项活动的参与者都来自 Friends 表。下面是查询结果格式的例子：

题解

一：some函数的使用

SELECT activity FROM Friends GROUP BY activity HAVING
COUNT(Friends.id) > SOME(SELECT count(id) cnt FROM Friends GROUP BY activity)
AND COUNT(Friends.id) < SOME(SELECT count(id) cnt FROM Friends GROUP BY activity)

SELECT activity FROM Friends GROUP BY activity HAVING
COUNT(Friends.id) != 
(SELECT MIN(cnt) FROM (SELECT count(id) cnt FROM Friends GROUP BY activity)a)
AND COUNT(Friends.id) != 
(SELECT MAX(cnt) FROM (SELECT count(id) cnt FROM Friends GROUP BY activity)b)

1164. 指定日期的产品价格🔒

https://leetcode-cn.com/problems/product-price-at-a-given-date/

这张表的主键是 (product_id, change_date)。这张表的每一行分别记录了某产品在某个日期更改后的新价格。
写一段 SQL来查找在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10。查询结果格式如下例所示：

题解

一：

select distinct p.product_id, ifnull(b.price, 10) price
from Products p left join
(
select a.product_id, new_price price from
	(
	select product_id, max(change_date) change_date from Products 
	where  change_date <= '2019-08-16'
	group by product_id
	)a
left join Products p 
on p.product_id = a.product_id and a.change_date = p.change_date
)b
on p.product_id = b.product_id

b表：查询的是 2019-08-16之前的有过修改的价格，即有过修改的最终价格，那么还有商品未被修改过，他们应该是10，这一部分是通过原来的表join来取到未修改的id。时间效率不高

二：union ，法一中b表只取到了修改过的最新价格。法二换一种思路，若是有过修改的取最后一次修改日期；若是未经修改的取第一次修改日期（必定大于2019-08-16）即表a所做的事，如下图所示。然后用原来的表join，join的条件取相同的id，相同的修改日期，则可依据修改日期分情况取price。时间效率还不错。

select p.product_id, case 
when a.change_date > '2019-08-16' then 10
else p.new_price end price 
from Products p,
(
select product_id, max(change_date) change_date from Products 
where  change_date <= '2019-08-16'
group by product_id
union
select product_id, min(change_date) change_date from products
group by product_id having min(change_date) > '2019-08-16'
)a
where p.product_id = a.product_id and p.change_date = a.change_date

1193. 每月交易 I🔒

https://leetcode-cn.com/problems/monthly-transactions-i/

id 是这个表的主键。该表包含有关传入事务的信息。state 列类型为 “[”批准“，”拒绝“] 之一。
编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。查询结果格式如下所示：

题解

一：字符串函数left(trans_date,7)，从trans_date字段左边起取7个字符。

select left(trans_date,7) month, country, count(state) as trans_count,
sum(case when state = "approved" then 1 else 0 end) as approved_count,
sum(amount) as trans_total_amount, 
sum(case when state = "approved" then amount else 0 end) as approved_total_amount
from Transactions group by month, country

二：日期格式转换函数DATE_FORMAT(trans_date, '%Y-%m')函数。

select DATE_FORMAT(trans_date, '%Y-%d') month from Transactions

select DATE_FORMAT(trans_date, '%Y-%m') month, country, count(state) as trans_count,
sum(case when state = "approved" then 1 else 0 end) as approved_count,
sum(amount) as trans_total_amount, 
sum(case when state = "approved" then amount else 0 end) as approved_total_amount
from Transactions group by month, country

602. 好友申请 II ：谁有最多的好友🔒

https://leetcode-cn.com/problems/friend-requests-ii-who-has-the-most-friends/

在 Facebook 或者 Twitter 这样的社交应用中，人们经常会发好友申请也会收到其他人的好友申请。表 request_accepted 存储了所有好友申请通过的数据记录，其中， requester_id 和 accepter_id 都是用户的编号。

写一个查询语句，求出谁拥有最多的好友和他拥有的好友数目。对于上面的样例数据，结果为：

注意：保证拥有最多好友数目的只有 1 个人。好友申请只会被接受一次，所以不会有 requester_id 和 accepter_id 值都相同的重复记录。
解释：编号为 '3' 的人是编号为 '1'，'2' 和 '4' 的好友，所以他总共有 3 个好友，比其他人都多。

进阶：在真实世界里，可能会有多个人拥有好友数相同且最多，你能找到所有这些人吗？

题解

一：union 与union all的区别：union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；union All：对两个结果集进行并集操作，包括重复行，不进行排序；这边使用union all，因为a中的两张表可能会有重复的行，例如当accepter_id=requester_id，且计数也相同，则union会删除相同行。

select id, sum(cnt) as num from
(
    select requester_id id, count(accepter_id) cnt from request_accepted group by requester_id
    union all
    select accepter_id id, count(requester_id) cnt from request_accepted group by accepter_id
)a
group by id 
order by num desc  limit 1

select requester_id id, count(accepter_id) cnt from request_accepted group by requester_id
union all
select accepter_id id, count(requester_id) cnt from request_accepted group by accepter_id
order by id

select a.id, sum(cnt) as num from
(select distinct requester_id id from request_accepted
union 
select distinct accepter_id id from request_accepted)a
left join 
(select requester_id as id, count(accepter_id) as cnt from request_accepted group by requester_id
union all 
select accepter_id as id, count(accepter_id) as cnt from request_accepted group by accepter_id)b 
on a.id = b.id 
group by a.id
order by num desc limit 1

1112. 每位学生的最高成绩🔒

https://leetcode-cn.com/problems/highest-grade-for-each-student/

(student_id, course_id) 是该表的主键。

编写一个 SQL 查询，查询每位学生获得的最高成绩和它所对应的科目，若科目成绩并列，取 course_id 最小的一门。查询结果需按 student_id 增序进行排序。查询结果格式如下所示：

Enrollments 表：

题解

一：先查出每个学生的id和最大成绩然后联源数据表。

select e.student_id, min(course_id) as course_id, grade from
Enrollments e,
(select student_id, max(grade) max_grad from Enrollments group by student_id)a 
where e.student_id = a.student_id and e.grade = a.max_grad
group by e.student_id, grade order by e.student_id

二：用源数据表直接查

SELECT student_id,MIN(course_id) course_id,MAX(grade) grade FROM enrollments
WHERE (student_id,grade) IN (SELECT student_id,max(grade) FROM enrollments GROUP BY student_id)
GROUP BY student_id ORDER BY student_id ;

三：转自leetcode上的题解，使用limit固定排列顺序，https://leetcode-cn.com/problems/highest-grade-for-each-student/solution/dui-pai-hao-xu-de-shu-ju-fen-zu-cha-xun-by-hua-g/。

select t.student_id, t.course_id, t.grade
from
(
    select student_id, course_id, grade from Enrollments
    order by student_id asc, grade desc, course_id asc limit 1000
)t
group by t.student_id

这张表的主键是 (user1_id, user2_id)。这张表的每一行代表着 user1_id 和 user2_id 之间存在着朋友关系。
喜欢列表： Likes

这张表的主键是 (user_id, page_id)。这张表的每一行代表着 user_id 喜欢 page_id。
写一段 SQL 向user_id = 1 的用户，推荐其朋友们喜欢的页面。不要推荐该用户已经喜欢的页面。你返回的结果中不应当包含重复项。返回结果的格式如下例所示：

用户1 同用户2, 3, 4, 6 是朋友关系。推荐页面为：页面23 来自于用户2, 页面24 来自于用户3, 页面56 来自于用户3 以及页面33 来自于用户6。页面77 同时被用户2 和用户3 推荐。页面88 没有被推荐，因为用户1 已经喜欢了它。

题解

一：union 和子查询

select distinct(page_id) recommended_page from Likes 
where user_id in 
(select user1_id id from Friendship where user2_id = 1
union 
select user2_id id from Friendship where user1_id = 1)
and page_id not in (select page_id from Likes where user_id = 1)

二：UNION 思路，先找出1的所有朋友，即表a中所有的id。再join原表。

select distinct(page_id) recommended_page from Likes,
(select user1_id id from Friendship where user2_id = 1
union 
select user2_id id from Friendship where user1_id = 1)a
where user_id = id and page_id not in (select page_id from Likes where user_id = 1)

三：case when 来查询出其朋友

select distinct(page_id) recommended_page from Likes,
(select case
when user1_id = 1 then user2_id
when user2_id = 1 then user1_id end id from Friendship
where user1_id = 1 or user2_id = 1
)a
where user_id = id and page_id not in (select page_id from Likes where user_id = 1)

178. 分数排名

https://leetcode-cn.com/problems/rank-scores/

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

重要提示：对于 MySQL 解决方案，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号。例如 `Rank`

题解

一：自定义变量

select Score, cast(num as UNSIGNED) 'Rank' from
(select Score, 
case when @sc = Score then @num := @num
else @num := @num + 1 end num, @sc :=Score from
(select Score from Scores order by Score desc)a,
(select @num := 0, @sc := null)b)c

二：不连续排名函数

select Score, dense_rank() over (order by Score desc)  as `Rank`
from Scores;

585. 2016年的投资🔒

https://leetcode-cn.com/problems/investments-in-2016/

写一个查询语句，将 2016 年 (TIV_2016) 所有成功投资的金额加起来，保留 2 位小数。对于一个投保人，他在 2016 年成功投资的条件是：他在 2015 年的投保额 (TIV_2015) 至少跟一个其他投保人在 2015 年的投保额相同。他所在的城市必须与其他投保人都不同（也就是说维度和经度不能跟其他任何一个投保人完全相同）。
输入格式:
表 insurance 格式如下：

PID 字段是投保人的投保编号， TIV_2015 是该投保人在2015年的总投保金额， TIV_2016 是该投保人在2016年的投保金额， LAT 是投保人所在城市的维度， LON 是投保人所在城市的经度。

解释：就如最后一个投保人，第一个投保人同时满足两个条件：1. 他在 2015 年的投保金额 TIV_2015 为 '10' ，与第三个和第四个投保人在 2015 年的投保金额相同。2. 他所在城市的经纬度是独一无二的。第二个投保人两个条件都不满足。他在 2015 年的投资 TIV_2015 与其他任何投保人都不相同。且他所在城市的经纬度与第三个投保人相同。基于同样的原因，第三个投保人投资失败。所以返回的结果是第一个投保人和最后一个投保人的 TIV_2016 之和，结果是 45 。

题解

一：第一个条件：通过选出TIV_2015中出现过不止一次的标出；第二个条件，通过分组来找出，同一个经纬的只有一个投保人的PID。

select sum(TIV_2016) TIV_2016 from insurance where PID in
(select PID from insurance group by LAT, LON having count(PID) = 1)
and TIV_2015 in (select TIV_2015 from insurance group by TIV_2015 having count(PID) >= 2)

二：这边注意concat函数的使用，可以在比较中使用多个字段。

select sum(TIV_2016) TIV_2016 from insurance where concat(LAT, LON) 
in (SELECT concat(LAT, LON) from insurance group by LAT , LON having COUNT(PID) = 1)
and TIV_2015 in (select TIV_2015 from insurance group by TIV_2015 having count(PID) >= 2)

1341. 电影评分🔒

https://leetcode-cn.com/problems/movie-rating/

(movie_id, user_id) 是这个表的主键。这个表包含用户在其评论中对电影的评分 rating 。created_at 是用户的点评日期。
请你编写一组 SQL 查询：查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。查找在 2020 年 2 月平均评分最高的电影名称。如果出现平局，返回字典序较小的电影名称。查询分两行返回，查询结果格式如下例所示：

Daniel 和 Monica 都点评了 3 部电影（"Avengers", "Frozen 2" 和 "Joker"）但是 Daniel 字典序比较小。Frozen 2 和 Joker 在 2 月的评分都是 3.5，但是 Frozen 2 的字典序比较小。

题解

一：union all大法，注意要加括号。

(select name as results from Movie_Rating r left join Users u 
on r.user_id = u.user_id group by r.user_id order by count(movie_id) desc, name limit 1)
union all
(select title as results from Movie_Rating r left join Movies m 
on r.movie_id = m.movie_id 
where created_at like "2020-02-%"
group by r.movie_id order by avg(rating) desc, title limit 1)

一杯敬朝阳一杯敬月光

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Leetcode SQL(四）

612. 平面上的最近距离https://leetcode-cn.com/problems/shortest-distance-in-a-plane/表point_2d保存了所有点（多于 2 个点）的坐标 (x,y) ，这些点在平面上两两不重合。写一个查询语句找到两点之间的最近距离，保留 2 位小数。最近距离在点 (-1,-1) 和(-1,2) 之间，距离为 1.00 。所以输出应该为：注意：任意点之间的最远距离小于 10000 。题解一：两张表join，笛卡尔积，会产生..
复制链接

扫一扫

专栏目录