我希望有一个简单的答案。参赛者进行三场比赛。有些选手只参加一场比赛。我怎样才能给所有的竞争对手展示最终结果呢?
比赛1
+------+--------+
| name | result |
+------+--------+
| Ali | 30 |
| Bob | 28 |
| Cal | 26 |
+------+--------+
比赛2
+------+--------+
| name | result |
+------+--------+
| Ali | 32 |
| Bob | 31 |
| Dan | 24 |
+------+--------+
比赛3
+------+--------+
| name | result |
+------+--------+
| Eva | 23 |
| Dan | 25 |
+------+--------+
最终结果应该是这样的:
+------+--------+--------+--------+
| name | result | result | result |
+------+--------+--------+--------+
| Ali | 30 | 32 | |
| Bob | 28 | 31 | |
| Cal | 26 | | |
| Dan | | 24 | 25 |
| Eva | | | 23 |
+------+--------+--------+--------+
我遇到的问题是从多个表中按名称排序。
以下是示例数据:
CREATE TABLE race (name varchar(20), result int);
CREATE TABLE race1 LIKE race;
INSERT INTO race1 VALUES ('Ali', '30'), ('Bob', '28'), ('Cal', '26');
CREATE TABLE race2 like race;
insert INTO race2 VALUES ('Ali', '32'), ('Bob', '31'), ('Dan', '24');
CREATE TABLE race3 LIKE race;
INSERT INTO race3 VALUES ('Eva', '23'), ('Dan', '25');
非常感谢!
最佳答案
我们走!!!
select race1.name as name, race1.result, race2.result, race3.result from race1
left join race2 on race2.name = race1.name
left join race3 on race3.name = race1.name
union
select race2.name as name, race1.result, race2.result, race3.result from race2
left join race1 on race1.name = race2.name
left join race3 on race3.name = race2.name
union
select race3.name as name, race1.result, race2.result, race3.result from race3
left join race1 on race1.name = race3.name
left join race2 on race2.name = race3.name;
正在工作:)
关于mysql - 从具有不同行数的表中选择,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/40653825/