php - MySQL - 按总时间排序结果

Question

我有三张表(参赛者、赛段和时间)

运行者表:

+--+----+
|id|name|
+--+----+
|1 |Karl|
+--+----+
|2 |Lou |
+--+----+

阶段表:

+--+-----+-----+---+
|id|name |order|end|
+--+-----+-----+---+
|1 |start|  1  | 0 |
+--+-----+-----+---+
|2 |bike |  2  | 0 |
+--+-----+-----+---+
|3 |run  |  3  | 0 |
+--+-----+-----+---+
|4 |end  |  4  | 1 |
+--+-----+-----+---+

运行者数据(时间)表:

+------+-----+-----+
|runner|stage|time |
+------+-----+-----+
|   1  |  1  |10:00|
+------+-----+-----+
|   1  |  2  |10:30|
+------+-----+-----+
|   1  |  3  |11:00|
+------+-----+-----+
|   2  |  1  |10:00|
+------+-----+-----+
|   2  |  2  |10:43|
+------+-----+-----+
|   2  |  3  |11:56|
+------+-----+-----+
|   1  |  4  |12:14|
+------+-----+-----+
|   2  |  4  |12:42|
+------+-----+-----+

嗯...那么我现在想要得到的结果如下(按总时间排序):

+------+-----+-----+-----+-----+----------+
|runner|start|bike |run  | end |   Total  |
+------+-----+-----+-----+-----+----------+
| Karl |10:00|10:30|11:00|12:14| 01:44:00 |   <--- FIRST( one hour)
+------+-----+-----+-----+-----+----------+
| Lou  |10:30|10:30|11:56|12:42| 02:12:00 |   <--- SECONDS( two hours )
+------+-----+-----+-----+-----+----------+

知道如何实现吗？问候!

Answer 1

以下应该有效(时间以秒为单位，而不是 HH:MM:SS)

select r.name, rd_start.time as start, rd_bike.time as bike, rd_run.time as run, rd_end.time as end,  from runner as r, rd_start.time+rd_bike.time+rd_run.time+rd_end.time as total
inner join runnerdata as rd_start on r.id=rd_start.runner and rd_start.stage=1
inner join runnerdata as rd_bike on r.id=rd_bike.runner and rd_start.stage=2
inner join runnerdata as rd_run on r.id=rd_run.runner and rd_start.stage=3
inner join runnerdata as rd_end on r.id=rd_end.runner and rd_start.stage=4
order by (rd_start.time+rd_bike.time+rd_run.time+rd_end.time)

(如果您发布创建表或者更好地使用此工具:http://sqlfiddle.com/ 这将使我们更容易测试我们的语句)