我有以下表结构:

火柴:

+-------------------+---------------------------+------+-----+-------------------+-----------------------------+
| Field             | Type                      | Null | Key | Default           | Extra                       |
+-------------------+---------------------------+------+-----+-------------------+-----------------------------+
| id                | bigint(20)                | NO   | PRI | NULL              | auto_increment              |
| creator_id        | bigint(20)                | NO   |     | NULL              |                             |
| mode              | enum('versus','freeplay') | NO   |     | NULL              |                             |
| name              | varchar(100)              | NO   |     | NULL              |                             |
| team_1_id         | varchar(100)              | NO   |     | NULL              |                             |
| team_2_id         | varchar(100)              | NO   |     | NULL              |                             |
+-------------------+---------------------------+------+-----+-------------------+-----------------------------+


团队:

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| creator_id   | bigint(20)   | NO   | MUL | NULL    |                |
| name         | varchar(100) | NO   |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+


我需要一个查询,我们从比赛表中获取所有比赛以及从球队表中获得球队的名称,假设模式为“对”时,球队名称取自球队表,但模式为“自由比赛”时”团队本身的名称是team_1_id或team_2_id(它们可以保存字符串,这就是为什么它们是varchar而不是int的原因)而无需进入teams表。

最佳答案

采用:

SELECT m.id,
       m.creator_id,
       m.mode,
       m.name,
       m.team_1_id,
       m.team_2_id
  FROM MATCHES m
 WHERE m.mode = 'freeplay'
UNION ALL
   SELECT m.id,
          m.creator_id,
          m.mode,
          m.name,
          t1.name,
          t2.name
     FROM MATCHES m
LEFT JOIN TEAMS t1 ON t1.id = m.team_1_id
LEFT JOIN TEAMS t2 ON t2.id = m.team_2_id
    WHERE m.mode = 'versus'

关于sql - MySQL查询:假设条件为…,则从比赛表中获取所有比赛以及从球队表中获得球队的名称。,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/3846583/

10-11 05:20