题目 : https://leetcode.cn/problems/friend-requests-ii-who-has-the-most-friends/
数据
Create table If Not Exists RequestAccepted (
requester_id int not null comment '发送好友请求ID',
accepter_id int null comment '接收好友ID',
accept_date date null comment '通过的日期'
)
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')
需求
查询有最多的好友的人和他拥有的好友数目
查询结果 :
| id | num |
| 3 | 3 |
解决
思路 :
- 求发送好友的总数, 通过时间不能为空
- 求接收好友的总数
- 对俩结果进行水平拼接
- 根据id 进行聚合求和,排序求最高的好友总数
-- 水平拼接发送好友总数,接收好友总数
with t1 as (
select requester_id as id,
count(*) as ct
from RequestAccepted
where accept_date is not null
group by requester_id
union all
select accepter_id as id,
count(*) as ct
from RequestAccepted
where accept_date is not null
group by accepter_id
)
-- 求和,求top1
select id,
sum(ct) as num
from t1
group by id
order by num desc
limit 1;