代码之家 › 专栏 › 技术社区 › GeekTantra

MySQL查询帮助

mysql sql

GeekTantra · 技术社区 · 14 年前

我有以下表格结构:

比赛:

+-------------------+---------------------------+------+-----+-------------------+-----------------------------+
| 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    |                |
+--------------+--------------+------+-----+---------+----------------+

我需要一个查询,在这里我们从matches表中获取所有匹配项以及teams表中的团队名称,假设当模式为“vs”时,团队名称取自teams表,但当模式为“freeplay”时,团队名称为team_1_id或team_2_id本身(它们可以保存字符串,这就是为什么它们是varchar instea的原因d of int)不去小组会议桌。

在这方面的任何帮助都将是非常可观的。

谢谢。

3 回复 | 直到 14 年前

OMG Ponies 14 年前

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'

methodin 14 年前

Robert Kluin 14 年前

SELECT mode,
       IF(team_1.id IS NULL, team_1_id, team_1.name),
       IF(team_2.id IS NULL, team_2_id, team_2.name),
  FROM matches
       LEFT JOIN teams AS team_1 ON (matches.team_id_1=team_1.id)
       LEFT JOIN teams AS team_2 ON (matches.team_id_2=team_2.id);

SELECT mode,
       IF(mode = 'freeplay' OR team_1.id IS NULL, team_1_id, team_1.name),
       IF(mode = 'freeplay' OR team_2.id IS NULL, team_2_id, team_2.name),
  FROM matches
       LEFT JOIN teams AS team_1 ON (matches.team_id_1=team_1.id)
       LEFT JOIN teams AS team_2 ON (matches.team_id_2=team_2.id);