SQL 微信运动步数好友排名：UNION ALL + ROW_NUMBER 分组排序（腾讯面试题）
一、题目背景
这道题来自腾讯微信事业群的数据开发岗面试。微信运动是微信内置的运动记录功能，用户每天可以看到自己的步数以及好友的步数排行榜。排行榜的核心逻辑是：给定用户和好友关系，计算每个用户在各自好友圈中的步数排名。这道题考察的就是如何在关系表中补齐自身数据，再用窗口函数进行分组排名。
业务场景：微信运动每晚8点会推送"今日步数好友排名"消息，需要根据用户的步数数据 + 好友关系数据，实时计算出每个用户在好友中的步数排名。这道题的 SQL 逻辑就是该推送功能的排名字段计算。
二、题目
有两个表，朋友关系表 t6_user_friend 和用户步数表 t6_user_steps。朋友关系表包含用户ID和好友ID；用户步数表包含用户ID和步数。请计算每个用户在各自好友中的步数排名（自己也算在内）。
朋友关系表 t6_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 |
+----------+------------+
用户步数表 t6_user_steps：
+----------+--------+
| user_id | steps |
+----------+--------+
| 1 | 100 |
| 2 | 95 |
| 3 | 90 |
| 4 | 80 |
| 5 | 10 |
+----------+--------+
三、思路分析
这是关系表补全 + 窗口函数排名的基础题型，核心是 UNION ALL 补全自身 + ROW_NUMBER 分组排序 + 自筛选：
- 补全自身数据：好友关系表中只有"用户→好友"的关系，但排名需要包含用户自己。用
UNION ALL将SELECT user_id, user_id AS friend_id, steps FROM t6_user_steps追加到好友步数结果中 - 分组排名：用
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY steps DESC)按用户分组、按步数降序排名 - 筛选自身排名：
WHERE user_id = friend_id过滤出每个用户自己的排名记录
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：UNION ALL 拼接好友步数和自身步数
先将好友关系表 JOIN 步数表获取好友的步数，再 UNION ALL 追加用户自身的步数（user_id 同时作为 friend_id）。
执行SQL
-- 好友步数
select t1.user_id, t1.friend_id, t2.steps
from t6_user_friend t1
join t6_user_steps t2
on t1.friend_id = t2.user_id
union all
-- 自身步数
select user_id, user_id as friend_id, steps
from t6_user_steps
执行结果
+----------+------------+--------+
| user_id | friend_id | 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 |
+----------+------------+--------+
20 rows selected (0.571 seconds)(https://www.dwsql.com)
步骤2：按用户分组，用 ROW_NUMBER 对好友步数排名
对步骤1的结果，按 user_id 分组、按 steps 降序排名，得到每个用户好友圈内所有成员的步数排名。
执行SQL
select user_id,
friend_id,
steps,
row_number() over (partition by user_id order by steps desc) as row_num
from (
-- 好友步数
select t1.user_id, t1.friend_id, t2.steps
from t6_user_friend t1
join t6_user_steps t2
on t1.friend_id = t2.user_id
union all
-- 自身步数
select user_id, user_id as friend_id, steps
from t6_user_steps
) tt1
执行结果
+----------+------------+--------+----------+
| user_id | friend_id | 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 |
+----------+------------+--------+----------+
20 rows selected (1.128 seconds)(https://www.dwsql.com)
步骤3：筛选出自身排名，得到最终结果
在步骤2的基础上，通过 WHERE user_id = friend_id 过滤出每个用户自己对应的那一条排名记录。
执行SQL
select user_id, row_num
from (
select user_id,
friend_id,
steps,
row_number() over (partition by user_id order by steps desc) as row_num
from (
-- 好友步数
select t1.user_id, t1.friend_id, t2.steps
from t6_user_friend t1
join t6_user_steps t2
on t1.friend_id = t2.user_id
union all
-- 自身步数
select user_id, user_id as friend_id, steps
from t6_user_steps
) tt1
) tt2
where user_id = friend_id
执行结果
+----------+----------+
| user_id | row_num |
+----------+----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
| 4 | 3 |
| 5 | 4 |
+----------+----------+
5 rows selected (0.777 seconds)(https://www.dwsql.com)
五、常见坑点
坑1：忘记 UNION ALL 自身数据
好友关系表只存了"用户→好友"的映射，如果不 UNION ALL 用户自己的步数记录，排名中就没有自己。排行榜中需要看到自己排在什么位置，所以必须把 user_id, user_id AS friend_id, steps 追加进来。
坑2：ROW_NUMBER vs RANK vs DENSE_RANK
如果两个好友步数相同，ROW_NUMBER() 会随机给其中一个排1、另一个排2（并列时不重复）；RANK() 会给相同步数的人排相同名次，但下一个跳过（1,1,3）；DENSE_RANK() 不跳过（1,1,2）。本题没有出现并列步数，所以 ROW_NUMBER 和 RANK 结果一致，但实际业务中应根据需求选择。
坑3：WHERE 条件放在外层而不是内层
不能在子查询中直接 WHERE user_id = friend_id 后再开窗排名——这样会把好友数据全过滤掉，只剩下自己一条记录，ROW_NUMBER 的结果永远是1。必须先在完整数据集上排名，再到最外层筛选。
六、举一反三
- 使用 RANK() 处理并列名次：如果步数可能相同，将
ROW_NUMBER()换成RANK()可以得到更合理的并列排名（如两人并列第2，下一个人是第4名） - 按日期维度统计每日排名：增加
date字段，PARTITION BY user_id, date ORDER BY steps DESC，可产出每个用户每日在好友中的步数排名趋势 - DENSE_RANK 连续排名：用
DENSE_RANK()实现连续排名（1,2,2,3...），适合前端展示"第N名"的榜单场景 - 好友步数 TOP-N：在步骤2的结果上
WHERE row_num <= 3即可得到每个用户好友圈内步数最高的TOP3好友
七、知识点总结
| 考点 | 说明 |
|---|---|
| UNION ALL | 将好友步数和自身步数合并为同一个数据集 |
| ROW_NUMBER 窗口函数 | 按 user_id 分组、按 steps 降序，为每个用户的好友圈排名 |
| 子查询嵌套 | 三层嵌套：拼接数据 → 排名 → 筛选自身记录 |
| 自筛选技巧 | user_id = friend_id 巧妙地从完整排名中定位到用户自己的名次 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t6_user_friend (
user_id int COMMENT '用户ID',
friend_id int COMMENT '好友ID'
) COMMENT '好友关系表';
CREATE TABLE t6_user_steps (
user_id int COMMENT '用户ID',
steps int COMMENT '步数'
) COMMENT '用户步数表';
INSERT INTO t6_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);
INSERT INTO t6_user_steps VALUES
(1,100),(2,95),(3,90),(4,80),(5,10);
「数据仓库技术」文章同步更新，不错过每一篇干货
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
