您的位置:首页 > 新闻 > 热点要闻 > SQL面试题练习 —— 微信运动步数在好友中的排名

SQL面试题练习 —— 微信运动步数在好友中的排名

2024/12/23 19:29:13 来源:https://blog.csdn.net/hu_wei123/article/details/140018109  浏览:    关键词:SQL面试题练习 —— 微信运动步数在好友中的排名

目录

  • 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        |
+----------+----------+

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com