SQL多表关联查询：获取指定玩家参与的对局单条完整数据

实现思路 #

• 直接从GAMES表过滤目标玩家参与的对局，避免关联CROSS表导致同局生成重复行
• 两次关联PLAYERS表，分别匹配player1、player2对应的别名，确保单条对局仅返回一行结果

具体SQL代码 #

SELECT 
    g.ID AS game_ID,
    g.player1_ID,
    g.player2_ID,
    g.des,
    p1.alias AS alias1,
    p2.alias AS alias2
FROM GAMES g
LEFT JOIN PLAYERS p1 ON g.player1_ID = p1.ID
LEFT JOIN PLAYERS p2 ON g.player2_ID = p2.ID
WHERE g.player1_ID = 23 OR g.player2_ID = 23;

结果验证 #

针对示例数据执行后返回结果如下，完全符合预期要求：

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