编写高效SQL查询：获取玩家当前参与及受邀游戏

需求 #

编写性能合理的SQL查询，针对每个玩家展示其当前参与的游戏及受邀游戏。

表结构与测试数据 #

PLAYERS表 & GAMES表 #

-- PLAYERS
ID | Name
---|------
1  | Abby
2  | Billy
3  | Cary
4  | Dolly
5  | Eddy

-- GAMES
ID | Name
---|----------
1  | Baccarat
2  | BlackJack
3  | Craps
4  | Roulette

LIST表 & INVITE表 #

-- LIST
ID
---
1
2
3
4
5
6
7
8

-- INVITE
ID | LIST | PLAYER | GAME
---|------|--------|-----
1  | 1    | 1      | 2
2  | 1    | 2      | 2
3  | 1    | 3      | 2
4  | 1    | 4      | 2
5  | 1    | 5      | 2
6  | 2    | 1      | 2
7  | 2    | 2      | 2
8  | 2    | 3      | 2
9  | 2    | 4      | 2
10 | 3    | 1      | 4
11 | 3    | 2      | 4
12 | 3    | 5      | 4
13 | 4    | 4      | 1
14 | 4    | 5      | 1
15 | 5    | 1      | 4
16 | 5    | 4      | 4
17 | 5    | 5      | 4
18 | 6    | 4      | 4
19 | 6    | 5      | 4
20 | 7    | 1      | 4
21 | 7    | 2      | 4
22 | 7    | 3      | 4
23 | 7    | 4      | 4
24 | 7    | 5      | 4
25 | 8    | 1      | 3
26 | 8    | 2      | 3
27 | 8    | 3      | 3
28 | 8    | 4      | 3

ROUND表 #

ID | Game | List | State
---|------|------|--------
1  | 2    | 1    | FINISHED
2  | 2    | 2    | FINISHED
3  | 2    | 2    | STARTED
4  | 4    | 3    | STARTED
5  | 3    | 8    | STARTED

业务规则 #

• 玩家通过邀请列表受邀参与游戏，列表可随时更新。
• 游戏回合开始前可多次更新列表；STARTED标记表示游戏正在进行，FINISHED表示已结束。
• 玩家登录时需展示两类游戏：
  1. 当前正在进行的游戏；
  2. 最新邀请列表中的受邀游戏；
• 已结束的游戏仅当玩家在最新邀请列表中再次受邀时，才需要展示。

现有问题 #

已经分别写出了三类查询，但无法合并成一个统一的查询，同时不确定现有方案的性能是否合理：

1. 查询已结束游戏的SQL：

SELECT * FROM GAMES G 
JOIN INVITE I ON I.GAME = G.ID
JOIN PLAYERS P ON P.ID   = I.PLAYER
JOIN ROUND  R ON G.ID   = R.GAME
WHERE R.STATE = 'FINISHED'

2. 查询当前进行中游戏的SQL：

SELECT * FROM GAMES G 
JOIN INVITE I ON I.GAME = G.ID
JOIN PLAYERS P ON P.ID   = I.PLAYER
JOIN ROUND  R ON G.ID   = R.GAME
WHERE R.STATE = 'STARTED'

3. 查询最新受邀游戏的SQL：

SELECT * FROM GAMES G 
JOIN INVITE I ON I.GAME = G.ID
JOIN PLAYERS P ON P.ID   = I.PLAYER
WHERE I.LIST            = (
  SELECT MAX(LL.ID) 
  FROM LIST LL 
  WHERE         LL.GAME = G.ID
)

解决方案 #

1. 合并查询的实现 #

通过UNION DISTINCT合并两类需要展示的游戏（进行中游戏 + 最新受邀游戏），同时自动去重（比如某个游戏既是进行中又是最新受邀）：

WITH latest_game_lists AS (
    -- 预计算每个游戏的最新邀请列表ID，避免重复子查询
    SELECT GAME, MAX(ID) AS latest_list_id
    FROM LIST
    GROUP BY GAME
),
player_current_games AS (
    -- 获取玩家当前正在进行的游戏
    SELECT DISTINCT
        P.ID AS player_id,
        P.Name AS player_name,
        G.ID AS game_id,
        G.Name AS game_name,
        'CURRENT' AS game_type
    FROM PLAYERS P
    JOIN INVITE I ON P.ID = I.PLAYER
    JOIN GAMES G ON I.GAME = G.ID
    JOIN ROUND R ON G.ID = R.GAME
    WHERE R.STATE = 'STARTED'
),
player_invited_games AS (
    -- 获取玩家在最新邀请列表中的受邀游戏
    SELECT DISTINCT
        P.ID AS player_id,
        P.Name AS player_name,
        G.ID AS game_id,
        G.Name AS game_name,
        'INVITED' AS game_type
    FROM PLAYERS P
    JOIN INVITE I ON P.ID = I.PLAYER
    JOIN GAMES G ON I.GAME = G.ID
    JOIN latest_game_lists LGL ON G.ID = LGL.GAME AND I.LIST = LGL.latest_list_id
)
-- 合并结果集并排序
SELECT * FROM player_current_games
UNION DISTINCT
SELECT * FROM player_invited_games
ORDER BY player_id, game_type, game_id;

2. 性能优化建议 #

为确保查询具备合理性能，需添加以下索引：

• PLAYERS(ID)：主键索引（通常默认已存在）
• GAMES(ID)：主键索引（通常默认已存在）
• INVITE(PLAYER, GAME, LIST)：复合索引，覆盖玩家、游戏、列表的关联查询
• ROUND(GAME, STATE)：复合索引，快速筛选特定状态的游戏回合
• LIST(GAME, ID)：复合索引，加速每个游戏最新列表ID的计算

另外，避免使用SELECT *，明确指定需要返回的字段，减少数据传输和内存消耗。

内容的提问来源于stack exchange，提问作者Jan Nielsen