SQL 跨服对战匹配：按战力分段的对手匹配算法（网易面试题）
一、题目背景
这道题来自网易游戏技术部的数据开发岗面试。竞技类游戏的跨服对战系统是保证匹配公平性和匹配速度的关键——如果匹配到同服玩家，可能出现"高分段互刷"的作弊行为；如果匹配到分差过大的对手，比赛就没有悬念，影响双方体验。因此需要一套算法：排除同服玩家后，找排名分最接近的对手。
业务场景：匹配系统每秒处理数千个匹配请求。离线层负责为匹配池中的玩家预计算"最优对手候选"，写入 Redis 供在线匹配服务直接读取。这道题的 CROSS JOIN + ROW_NUMBER 分差排名就是离线计算的核心 SQL。
二、题目
现有一个跨服对战匹配系统，包含两张表：
t1_player_rank：各服务器玩家的排名分t1_match_pool：匹配池，记录正在等待匹配的玩家
请为匹配池中每位玩家找出排名分最接近（分差最小）且不在同一服务器的对手。如果存在多个分差相同的对手，选择排名分较高的那个。
玩家排名分表 t1_player_rank：
+-----------+-------------+--------+
| player_id | server_id | score |
+-----------+-------------+--------+
| P001 | S1 | 1500 |
| P002 | S1 | 1600 |
| P003 | S2 | 1520 |
| P004 | S2 | 1580 |
| P005 | S3 | 1480 |
| P006 | S3 | 1620 |
| P007 | S1 | 1550 |
| P008 | S2 | 1490 |
| P009 | S3 | 1610 |
| P010 | S1 | 1530 |
+-----------+-------------+--------+
匹配池表 t1_match_pool：
+-----------+
| player_id |
+-----------+
| P001 |
| P003 |
| P005 |
+-----------+
三、思路分析
本题难度较高，考察多表 JOIN、子查询和排名窗口函数的综合运用。关键在于找出每个匹配玩家的最优对手。
解题步骤：
- 将匹配池表与排名分表 JOIN 获取等待匹配玩家的信息；
- 将等待玩家与所有其他玩家（排除同服）做 CROSS JOIN 计算分差；
- 使用
ROW_NUMBER()按分差升序、对手分数降序排序，取排名第一的即为最优对手。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 计算等待玩家与所有候选对手的分差
执行SQL
select m.player_id as waiting_player,
m.score as waiting_score,
m.server_id as waiting_server,
r.player_id as opponent,
r.score as opponent_score,
r.server_id as opponent_server,
abs(m.score - r.score) as score_diff
from (
select p.player_id, p.server_id, p.score
from t1_match_pool mp
join t1_player_rank p on mp.player_id = p.player_id
) m
join t1_player_rank r
on m.player_id != r.player_id
and m.server_id != r.server_id
执行结果
+-----------------+----------------+-----------------+-----------+-----------------+------------------+-------------+
| waiting_player | waiting_score | waiting_server | opponent | opponent_score | opponent_server | score_diff |
+-----------------+----------------+-----------------+-----------+-----------------+------------------+-------------+
| P001 | 1500 | S1 | P003 | 1520 | S2 | 20 |
| P001 | 1500 | S1 | P004 | 1580 | S2 | 80 |
| P001 | 1500 | S1 | P005 | 1480 | S3 | 20 |
| P001 | 1500 | S1 | P006 | 1620 | S3 | 120 |
| P001 | 1500 | S1 | P008 | 1490 | S2 | 10 |
| P001 | 1500 | S1 | P009 | 1610 | S3 | 110 |
| P003 | 1520 | S2 | P001 | 1500 | S1 | 20 |
| P003 | 1520 | S2 | P002 | 1600 | S1 | 80 |
| P003 | 1520 | S2 | P005 | 1480 | S3 | 40 |
| P003 | 1520 | S2 | P006 | 1620 | S3 | 100 |
| P003 | 1520 | S2 | P007 | 1550 | S1 | 30 |
| P003 | 1520 | S2 | P009 | 1610 | S3 | 90 |
| P003 | 1520 | S2 | P010 | 1530 | S1 | 10 |
| P005 | 1480 | S3 | P001 | 1500 | S1 | 20 |
| P005 | 1480 | S3 | P002 | 1600 | S1 | 120 |
| P005 | 1480 | S3 | P003 | 1520 | S2 | 40 |
| P005 | 1480 | S3 | P004 | 1580 | S2 | 100 |
| P005 | 1480 | S3 | P007 | 1550 | S1 | 70 |
| P005 | 1480 | S3 | P008 | 1490 | S2 | 10 |
| P005 | 1480 | S3 | P010 | 1530 | S1 | 50 |
+-----------------+----------------+-----------------+-----------+-----------------+------------------+-------------+
20 rows selected (1.367 seconds)(https://www.dwsql.com)
2. 使用 ROW_NUMBER 选出最优对手
执行SQL
select waiting_player,
waiting_score,
waiting_server,
opponent,
opponent_score,
opponent_server,
score_diff
from (
select m.player_id as waiting_player,
m.score as waiting_score,
m.server_id as waiting_server,
r.player_id as opponent,
r.score as opponent_score,
r.server_id as opponent_server,
abs(m.score - r.score) as score_diff,
row_number() over (
partition by m.player_id
order by abs(m.score - r.score) asc, r.score desc
) as rn
from (
select p.player_id, p.server_id, p.score
from t1_match_pool mp
join t1_player_rank p on mp.player_id = p.player_id
) m
join t1_player_rank r
on m.player_id != r.player_id
and m.server_id != r.server_id
) t
where rn = 1
执行结果
+-----------------+----------------+-----------------+-----------+-----------------+------------------+-------------+
| waiting_player | waiting_score | waiting_server | opponent | opponent_score | opponent_server | score_diff |
+-----------------+----------------+-----------------+-----------+-----------------+------------------+-------------+
| P001 | 1500 | S1 | P008 | 1490 | S2 | 10 |
| P003 | 1520 | S2 | P010 | 1530 | S1 | 10 |
| P005 | 1480 | S3 | P008 | 1490 | S2 | 10 |
+-----------------+----------------+-----------------+-----------+-----------------+------------------+-------------+
3 rows selected (1.338 seconds)(https://www.dwsql.com)
五、常见坑点
坑1：CROSS JOIN 数据膨胀
匹配池 M 个玩家 JOIN 全部玩家 N 人（排除同服后约 N×0.7），中间结果 = M × (N - 同服人数)。如果全部玩家 10万人、匹配池 1万人，中间结果可能达到 1万 × 7万 = 7亿行。实际业务中需要限制匹配分数区间（如 BETWEEN score-200 AND score+200），在 JOIN 条件中提前过滤。
坑2：按分差升序 + 对手分降序的优先级
题目要求"分差相同选分数高的"，这在 ROW_NUMBER() ORDER BY score_diff ASC, opponent_score DESC 中容易写反顺序。注意是 ORDER BY score_diff ASC（差越小越好）排在第一位，opponent_score DESC（分数越高越好）只在分差相同时生效。
坑3：同服务器的对手已在 JOIN 中排除
JOIN 条件 m.server_id != r.server_id 只保证排除同服。但如果一个服的玩家人数很少，匹配池中该服的玩家可能没有跨服对手可选——此时 LEFT JOIN 后 opponent 为 NULL，需要在业务层处理"暂无匹配对手"的情况。
六、举一反三
-
分数区间限缩：在 JOIN 条件中加入
abs(m.score - r.score) <= 200，只匹配分数差在合理范围内（如200分以内）的对手，既减少中间数据量，也保证匹配质量 -
双向匹配互选：如果 A 的最优对手是 B，B 的最优对手也是 A，形成"双向最优匹配对"——这是匹配系统最理想的结果，可以对结果表做自连接验证
-
按段位分组匹配：先按分数排名分 10 个段位（NTILE），每个段位内部再做分差匹配，确保"王者只和王者匹配，青铜只和青铜匹配"
-
匹配等待超时放大：如果玩家的分差最小对手 > 200 分，说明段位内暂时没有合适的对手——放宽分数区间再试，第二次尝试按分差 500 内匹配，模拟真实匹配系统的"分段扩圈"机制
七、知识点总结
| 考点 | 说明 |
|---|---|
| CROSS JOIN + 过滤 | JOIN ... ON player_id != ... AND server_id != ... 生成候选对手集 |
| ABS() 分差绝对值 | ABS(m.score - r.score) 计算两个玩家的分数差距 |
| ROW_NUMBER 双字段排序 | ORDER BY score_diff ASC, opponent_score DESC 分差优先、分数兜底 |
| 子查询 + WHERE rn = 1 | 窗口函数在子查询中排名，外层取最优一行 |
八、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句
CREATE TABLE t1_player_rank (
player_id string COMMENT '玩家ID',
server_id string COMMENT '服务器ID',
score int COMMENT '排名分'
) COMMENT '玩家排名分表';
CREATE TABLE t1_match_pool (
player_id string COMMENT '等待匹配的玩家ID'
) COMMENT '跨服对战匹配池表';
-- 数据插入
INSERT INTO t1_player_rank VALUES
('P001', 'S1', 1500),
('P002', 'S1', 1600),
('P003', 'S2', 1520),
('P004', 'S2', 1580),
('P005', 'S3', 1480),
('P006', 'S3', 1620),
('P007', 'S1', 1550),
('P008', 'S2', 1490),
('P009', 'S3', 1610),
('P010', 'S1', 1530);
INSERT INTO t1_match_pool VALUES
('P001'),
('P003'),
('P005');
「数据仓库技术」文章同步更新，不错过每一篇干货
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
