目录
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,笛卡尔积,会产生个两两组合对,同时没法排除自身和自身的组合。不过好在题中交代这些点在平面上两两不重合,所以两两组合x和y不同时相等可以排除自身组合。这边用row_number函数给每一条记录生成一个id,join时用a的id join比b的id大的,可排除自己join,就算有重叠点也不影响,因为重叠点id毕竟不同,而且只有
个两两组合对。
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
1264. 页面推荐🔒
https://leetcode-cn.com/problems/page-recommendations/
这张表的主键是 (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)