我有以下表结构:
火柴:
+-------------------+---------------------------+------+-----+-------------------+-----------------------------+
| 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/