目录
- 1 题目
- 2 建表语句
- 3 题解
题目来源：腾讯。
1 题目
有两个表，朋友关系表user_friend，用户步数表user_steps。朋友关系表包含两个字段，用户id，用户好友的id；用户步数表包含两个字段，用户id，用户的步数.用户在好友中的排名
-- 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 |
+---------------------+-------------------+
2 建表语句
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);
3 题解
（1）列出好友步数，并将自己步数添加到结果中
--好友步数
select t1.user_id, t1.friend_id, t2.steps
from user_friend t1
join user_steps t2
on t1.friend_id = t2.user_id
union all
-- 自己步数
select user_id, user_id as friend_id, steps
from user_steps
执行结果
+--------------+----------------+------------+
| _u1.user_id | _u1.friend_id | _u1.steps |
+--------------+----------------+------------+
| 1 | 2 | 95 |
| 1 | 3 | 90 |
| 2 | 1 | 100 |
| 2 | 3 | 90 |
| 2 | 4 | 80 |
| 2 | 5 | 10 |
| 3 | 1 | 100 |
| 3 | 4 | 80 |
| 3 | 5 | 10 |
| 4 | 2 | 95 |
| 4 | 3 | 90 |
| 4 | 5 | 10 |
| 5 | 2 | 95 |
| 5 | 3 | 90 |
| 5 | 4 | 80 |
| 1 | 1 | 100 |
| 2 | 2 | 95 |
| 3 | 3 | 90 |
| 4 | 4 | 80 |
| 5 | 5 | 10 |
+--------------+----------------+------------+
（2）按照用户分组，给每个用户的“好友”进行排名
select tt1.user_id,tt1.friend_id,tt1.steps,row_number() over (partition by tt1.user_id order by tt1.steps desc) as row_num
from (--好友步数select t1.user_id,t1.friend_id,t2.stepsfrom user_friend t1join user_steps t2on t1.friend_id = t2.user_idunion all-- 自己步数select user_id,user_id as friend_id,stepsfrom user_steps) tt1
执行结果
+--------------+----------------+------------+----------+
| tt1.user_id | tt1.friend_id | tt1.steps | row_num |
+--------------+----------------+------------+----------+
| 1 | 1 | 100 | 1 |
| 1 | 2 | 95 | 2 |
| 1 | 3 | 90 | 3 |
| 2 | 1 | 100 | 1 |
| 2 | 2 | 95 | 2 |
| 2 | 3 | 90 | 3 |
| 2 | 4 | 80 | 4 |
| 2 | 5 | 10 | 5 |
| 3 | 1 | 100 | 1 |
| 3 | 3 | 90 | 2 |
| 3 | 4 | 80 | 3 |
| 3 | 5 | 10 | 4 |
| 4 | 2 | 95 | 1 |
| 4 | 3 | 90 | 2 |
| 4 | 4 | 80 | 3 |
| 4 | 5 | 10 | 4 |
| 5 | 2 | 95 | 1 |
| 5 | 3 | 90 | 2 |
| 5 | 4 | 80 | 3 |
| 5 | 5 | 10 | 4 |
+--------------+----------------+------------+----------+
（3）求取最终结果
select user_id,row_num
from (select tt1.user_id,tt1.friend_id,tt1.steps,row_number() over (partition by tt1.user_id order by tt1.steps desc) as row_numfrom (--好友步数select t1.user_id,t1.friend_id,t2.stepsfrom user_friend t1join user_steps t2on t1.friend_id = t2.user_idunion all-- 自己步数select user_id,user_id as friend_id,stepsfrom user_steps) tt1) tt2
where user_id = friend_id
执行结果
+----------+----------+
| user_id | row_num |
+----------+----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
| 4 | 3 |
| 5 | 4 |
+----------+----------+
