我希望有一个简单的答案。参赛者进行三场比赛。有些选手只参加一场比赛。我怎样才能给所有的竞争对手展示最终结果呢?
比赛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/

10-16 18:49