MySQL:从其他表中获取与ID匹配的多个数据

Question

我有两个表，一个是战斗，第二个是用户表结构和表中的记录如下user_id和competitor_id是用户表中的id

A. Battle table

   battle_id | user_id | competitor_id 
        1     | 1       |  5      
        2     | 3       |  4
        3     | 2       |  3

B.   User table

      ID | user_name 
      1  | ABC
      2  | XYZ
      3  | PQR
      4  | MNO
      5  | UVW

我想获取名称user_id和competitor_id输出应如下所示

输出

   battle_id | user_id | competitor_id | user_name |  competitor_name
        1     | 1       |  5           | ABC       | UVW
        2     | 3       |  4           | PQR       | MNO
        3     | 2       |  3           | XYZ       | PQR

Answer 1

两个连接就可以了:

SELECT b.battle_id, b.user_id, b.competitor_id, u1.user_name AS 'user_name', u2.user_name AS 'competitor_name'
FROM `Battle` AS b
JOIN `User` AS u1 ON u1.ID = b.user_id
JOIN `User` AS u2 ON u2.ID = b.competitor_id