mysql - 有条件地从加入 MySQL 中获取第二行

Question

我有以下 3 个表:

mysql> explain runners;
+-------------+------------------+
| Field       | Type             |
+-------------+------------------+
| runner_id   | int(10) unsigned |
| name        | varchar(100)     |
+-------------+------------------+

mysql> explain runs;
+-------------+------------------+
| Field       | Type             |
+-------------+------------------+
| run_id      | int(10) unsigned |
| runner_id   | int(10) unsigned |
| race_id     | int(10) unsigned |
| position    | int(10)          |
+-------------+------------------+

mysql> explain races;
+-------------+------------------+
| Field       | Type             |
+-------------+------------------+
| race_id     | int(10) unsigned |
| date        | datetime         |
+-------------+------------------+

接下来，我必须显示给定运行者参加的所有比赛，如下所示:

mysql> select runner.name, runs.run_id, runs.position, races.race_id
       from runners
       join runs on(runs.runner_id = runners.runner_id)
       join races on(races.race_id = run.run_id)
       where runners.runner_id = 1;
+------------------+--------+----------+---------+
| name             | run_id | position | race_id |
+------------------+--------+----------+---------+
| John Runner      |      1 |        1 |       1 |
| John Runner      |      7 |        3 |       2 |
| John Runner      |     15 |        2 |       3 |
................... more lines ...................
+------------------+--------+----------+---------+

现在我应该向这个查询添加另一个名为“win/second”的列，如果当前运行者的位置 = 1，它将显示第二名的运行者的名字，或者如果当前运行者的位置!= 1，但我只是不知道如何让它工作，甚至不知道从什么开始。

Answer 1

这应该可以满足您的需求:

select runners.name, 
   runs.run_id, 
   runs.position, 
   races.race_id,
   case
      when runs.position = 1
      then    runnerssecond.Name
      else    runnerswinner.Name
   end as NextColumn
from runners
   join runs on(runs.runner_id = runners.runner_id)
   join races on(races.race_id = runs.Race_Id)
   left join runs runswinner on runswinner.race_id = races.race_id and runswinner.Position = 1
   left join runners runnerswinner on runnerswinner.Runner_Id = runswinner.Runner_Id
   left join runs runsecond on runsecond.race_id = races.race_id and runsecond.Position = 2
   left join runners runnerssecond on runnerssecond.Runner_Id = runsecond.Runner_Id
where runners.runner_id = 1;

还有 SQL Fiddle .

祝你好运。