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

最新推荐文章于 2024-09-14 18:42:05 发布

夏木夕

最新推荐文章于 2024-09-14 18:42:05 发布

阅读量358

点赞数 2

分类专栏： SQL 文章标签： sql 数据库

本文链接：https://blog.csdn.net/hu_wei123/article/details/140018109

版权

SQL 专栏收录该内容

41 篇文章 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.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) 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_num
      from (
               --好友步数
               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) tt1) tt2
where user_id = friend_id

执行结果

+----------+----------+
| user_id  | row_num  |
+----------+----------+
| 1        | 1        |
| 2        | 2        |
| 3        | 2        |
| 4        | 3        |
| 5        | 4        |
+----------+----------+