力扣每日刷题SQL(585,597,601,602,603)

力扣_585

说明

点击跳转力扣

分析

实现

准备工作

Create Table If Not Exists Insurance (pid int, tiv_2015 float, tiv_2016 float, lat float, lon float);
Truncate table Insurance;
insert into Insurance (pid, tiv_2015, tiv_2016, lat, lon) values ('1', '10', '5', '10', '10');
insert into Insurance (pid, tiv_2015, tiv_2016, lat, lon) values ('2', '20', '20', '20', '20');
insert into Insurance (pid, tiv_2015, tiv_2016, lat, lon) values ('3', '10', '30', '20', '20');
insert into Insurance (pid, tiv_2015, tiv_2016, lat, lon) values ('4', '10', '40', '40', '40');
1
2
3
4
5
6

问题

/*
编写解决方案报告 2016 年 (tiv_2016) 所有满足下述条件的投保人的投保金额之和：
1.他在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
2.他所在的城市必须与其他投保人都不同（也就是说 (lat, lon) 不能跟其他任何一个投保人完全相同）。
3.tiv_2016 四舍五入的 两位小数
*/

实现方法

with t1 as (select *,
                   sum(1) over (partition by tiv_2015) sam_2015,
                   sum(1) over (partition by lat,lon)  sam_add
            from insurance)
select round(sum(tiv_2016), 2) as tiv_2016
from t1
where sam_2015 > 1
  and sam_add = 1;
1
2
3
4
5
6
7
8

小结

窗口函数

力扣_597

说明

点击跳转力扣

分析

本题的难点在理解题意。由于题目说明： 通过的好友申请不一定都在表 friend_request 中 。
因此不用再考虑表连接。统计通过的请求数除以总的请求数即可。
通过的请求数和总的请求数都要去重。
用IFNULL处理为NULL的情况。 ROUND函数取精度。

实现

准备工作

Create table If Not Exists FriendRequest (sender_id int, send_to_id int, request_date date);
Create table If Not Exists RequestAccepted (requester_id int, accepter_id int, accept_date date);
Truncate table FriendRequest;
insert into FriendRequest (sender_id, send_to_id, request_date) values ('1', '2', '2016/06/01');
insert into FriendRequest (sender_id, send_to_id, request_date) values ('1', '3', '2016/06/01');
insert into FriendRequest (sender_id, send_to_id, request_date) values ('1', '4', '2016/06/01');
insert into FriendRequest (sender_id, send_to_id, request_date) values ('2', '3', '2016/06/02');
insert into FriendRequest (sender_id, send_to_id, request_date) values ('3', '4', '2016/06/09');
Truncate table RequestAccepted;
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('1', '2', '2016/06/03');
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('1', '3', '2016/06/08');
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('2', '3', '2016/06/08');
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('3', '4', '2016/06/09');
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('3', '4', '2016/06/10');
1
2
3
4
5
6
7
8
9
10
11
12
13
14

问题

– 求出好友申请的通过率，用 2 位小数表示。通过率由接受好友申请的数目除以申请总数

实现方法

with accept as (select distinct requester_id, accepter_id
                from requestaccepted)
select ifnull(round((select count(*) from accept) /
                    (select count(*) from (select distinct sender_id, send_to_id from friendrequest) t), 2),
              0) as accept_rate;
1
2
3
4
5

小结

ifnull的使用

力扣_601

说明

点击跳转力扣

分析

实现

准备工作

Create table If Not Exists Stadium
(
    id         int,
    visit_date DATE NULL,
    people     int
);
Truncate table Stadium;
insert into Stadium (id, visit_date, people)
values ('1', '2017-01-01', '10');
insert into Stadium (id, visit_date, people)
values ('2', '2017-01-02', '109');
insert into Stadium (id, visit_date, people)
values ('3', '2017-01-03', '150');
insert into Stadium (id, visit_date, people)
values ('4', '2017-01-04', '99');
insert into Stadium (id, visit_date, people)
values ('5', '2017-01-05', '145');
insert into Stadium (id, visit_date, people)
values ('6', '2017-01-06', '1455');
insert into Stadium (id, visit_date, people)
values ('7', '2017-01-07', '199');
insert into Stadium (id, visit_date, people)
values ('8', '2017-01-09', '188');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

问题

– 编写解决方案找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。
– 返回按 visit_date 升序排列 的结果表

实现方法

with t1 as (select *,
                   id - rank() over (order by id) rk
            from stadium a
            where people >= 100)
select id, visit_date, people
from t1
where rk in (select rk from t1 group by rk having count(id) >= 3);
1
2
3
4
5
6
7

小结

窗口函数
聚合函数

力扣_602

说明

点击跳转力扣

分析

1.按照requester_id分组,统计数量;
2.按照accepter_id分组,统计数量;
3.把上面两个表用union all拼接起来,按照requester_id分组,找出发送请求和接收数量最多的,即为好友最多的人.

实现

准备工作

Create table If Not Exists RequestAccepted (requester_id int not null, accepter_id int null, accept_date date null);
Truncate table RequestAccepted;
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('1', '2', '2016/06/03');
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('1', '3', '2016/06/08');
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('2', '3', '2016/06/08');
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('3', '4', '2016/06/09');
1
2
3
4
5
6

问题

– 编写解决方案，找出拥有最多的好友的人和他拥有的好友数目

实现方法

with t1 as (select requester_id, count(requester_id) cnt
            from requestaccepted
            group by requester_id
            union all
            select accepter_id, count(requester_id) cnt
            from requestaccepted
            group by accepter_id)
select distinct requester_id                              id,
                sum(cnt) over (partition by requester_id) num
from t1
order by num desc;
1
2
3
4
5
6
7
8
9
10
11

小结

1.union和union all的区别:
两者都用于拼接两个表,union会去除重复行,然后拼接;union all则是全部拼接.
2.聚合函数

力扣_603

说明

点击跳转详情

分析

实现

这道题的实现方法与601思想一致,可以借鉴一下601的分析.

准备工作

Create table If Not Exists Cinema (seat_id int primary key auto_increment, free bool);
Truncate table Cinema;
insert into Cinema (seat_id, free) values ('1', '1');
insert into Cinema (seat_id, free) values ('2', '0');
insert into Cinema (seat_id, free) values ('3', '1');
insert into Cinema (seat_id, free) values ('4', '1');
insert into Cinema (seat_id, free) values ('5', '1');
1
2
3
4
5
6
7

问题

– 查找电影院所有连续可用的座位
– 返回按 seat_id 升序排序 的结果表

实现方法

with t1 as (select seat_id,
                   seat_id - row_number() over () as cnt
            from cinema
            where free = 1)
select seat_id
from t1
where cnt in (select cnt from t1 group by cnt having count(cnt) >= 2);
1
2
3
4
5
6
7

小结

窗口函数
聚合函数