一、题目
有两个表，朋友关系表user_friend，用户步数表user_steps。朋友关系表包含两个字段，用户id，用户好友的id；用户步数表包含两个字段，用户id，用户的步数
查询：占据多少个好友的封面（在好友的列表中排行第一，且必须超过好友的步数）
代码语言：javascript复制-- user_friend 数据
---------- ------------
| user_id | friend_id |
---------- ------------
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 3 |
| 2 | 4 |
| 2 | 5 |
| 3 | 1 |
| 3 | 4 |
| 3 | 5 |
| 4 | 2 |
| 4 | 3 |
| 4 | 5 |
| 5 | 2 |
| 5 | 3 |
| 5 | 4 |
---------- ------------
--user_friend数据
--------------------- -------------------
| user_steps.user_id | user_steps.steps |
--------------------- -------------------
| 1 | 100 |
| 2 | 95 |
| 3 | 90 |
| 4 | 80 |
| 5 | 10 |
--------------------- ------------------- 二、题目分析
1.解题思路
- 查询出用户的步数和好友的步数；
- 取出自己步数比好友步数多的记录;
- 按照friend_id进行分组排序，取出步数第一名的用户好友记录
- 关联用户好友表，计算占据封面个数；
2.难度分析
维度 评分
维度 | 评分 |
|---|---|
题目难度 | ⭐️⭐️⭐️⭐️ |
题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
三、SQL
1.计算好友的朋友步数及好友自己的步数
执行SQL
代码语言：javascript复制--
select t1.user_id,
t2.steps as user_steps, --自己步数
t1.friend_id,
t3.steps as friend_steps --好友步数
from user_friend t1
left join user_steps t2
on t1.user_id = t2.user_id
left join user_steps t3
on t1.friend_id = t3.user_id执行结果
代码语言：javascript复制 ------------- ------------- --------------- ---------------
| t1.user_id | user_steps | t1.friend_id | friend_steps |
------------- ------------- --------------- ---------------
| 1 | 100 | 2 | 95 |
| 1 | 100 | 3 | 90 |
| 2 | 95 | 1 | 100 |
| 2 | 95 | 3 | 90 |
| 2 | 95 | 4 | 80 |
| 2 | 95 | 5 | 10 |
| 3 | 90 | 1 | 100 |
| 3 | 90 | 4 | 80 |
| 3 | 90 | 5 | 10 |
| 4 | 80 | 2 | 95 |
| 4 | 80 | 3 | 90 |
| 4 | 80 | 5 | 10 |
| 5 | 10 | 2 | 95 |
| 5 | 10 | 3 | 90 |
| 5 | 10 | 4 | 80 |
------------- ------------- --------------- --------------- 2.取出自己步数比好友步数多的记录
执行SQL
代码语言：javascript复制select user_id,
user_steps,
friend_id,
friend_steps
from (select t1.user_id,
t2.steps as user_steps, --自己步数
t1.friend_id,
t3.steps as friend_steps --好友步数
from user_friend t1
left join user_steps t2
on t1.user_id = t2.user_id
left join user_steps t3
on t1.friend_id = t3.user_id) t
where user_steps > friend_steps执行结果
代码语言：javascript复制 ---------- ------------- ------------ ---------------
| user_id | user_steps | friend_id | friend_steps |
---------- ------------- ------------ ---------------
| 1 | 100 | 2 | 95 |
| 1 | 100 | 3 | 90 |
| 2 | 95 | 3 | 90 |
| 2 | 95 | 4 | 80 |
| 2 | 95 | 5 | 10 |
| 3 | 90 | 4 | 80 |
| 3 | 90 | 5 | 10 |
| 4 | 80 | 5 | 10 |
---------- ------------- ------------ --------------- 3.按照friend_id进行分组排序，取出步数第一名的用户好友记录
注意：这里使用了row_number，可以使用rank或者dense_rank 具体根据需要进行选择；
执行SQL
代码语言：javascript复制select user_id,
user_steps,
friend_id,
friend_steps
from (select user_id,
user_steps,
friend_id,
friend_steps,
row_number() over (partition by friend_id order by user_steps desc ) rn
from (select t1.user_id,
t2.steps as user_steps, --自己步数
t1.friend_id,
t3.steps as friend_steps --好友步数
from user_friend t1
left join user_steps t2
on t1.user_id = t2.user_id
left join user_steps t3
on t1.friend_id = t3.user_id) t
where user_steps > friend_steps) tt
where rn = 1执行结果
代码语言：javascript复制 ---------- ------------- ------------ ---------------
| user_id | user_steps | friend_id | friend_steps |
---------- ------------- ------------ ---------------
| 1 | 100 | 2 | 95 |
| 1 | 100 | 3 | 90 |
| 2 | 95 | 4 | 80 |
| 2 | 95 | 5 | 10 |
---------- ------------- ------------ --------------- 4.关联用户好友表，计算占据封面个数；
执行SQL
代码语言：javascript复制select ttt1.user_id,
count(ttt.friend_id) as fm_cnt
from user_friend ttt1
left join(select user_id,
user_steps,
friend_id,
friend_steps
from (select user_id,
user_steps,
friend_id,
friend_steps,
row_number() over (partition by friend_id order by user_steps desc ) rn
from (select t1.user_id,
t2.steps as user_steps, --自己步数
t1.friend_id,
t3.steps as friend_steps --好友步数
from user_friend t1
left join user_steps t2
on t1.user_id = t2.user_id
left join user_steps t3
on t1.friend_id = t3.user_id) t
where user_steps > friend_steps) tt
where rn = 1) ttt
on ttt1.user_id = ttt.user_id
and ttt1.friend_id = ttt.friend_id
group by ttt1.user_id执行结果
代码语言：javascript复制 --------------- ---------
| ttt1.user_id | fm_cnt |
--------------- ---------
| 1 | 2 |
| 2 | 2 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
--------------- --------- 四、建表语句和数据插入
代码语言：javascript复制-- 创建好友关系表
CREATE TABLE user_friend
(
user_id INT,
friend_id INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY 't';
-- 插入数据
INSERT INTO user_friend
VALUES (1, 2),
(1, 3),
(2, 1),
(2, 3),
(2, 4),
(2, 5),
(3, 1),
(3, 4),
(3, 5),
(4, 2),
(4, 3),
(4, 5),
(5, 2),
(5, 3),
(5, 4);
CREATE TABLE user_steps
(
user_id INT,
steps INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY 't';
INSERT INTO user_steps
VALUES (1, 100),
(2, 95),
(3, 90),
(4, 80),
(5, 10);
