MySQL 从满足条件的行中选择字段，并从上面的行中选择字段

Question

我有一个结果数据表，我想在其中选择主要针对与第二名完成者相关的记录的信息。

因此，例如，比赛地点、比赛日期以及获胜者的时间差。

但我想在我的查询中显示的是获胜者及其获胜差值，该差值实际上存储在与第二名完成者相关的记录中。

例如:

id | race_id | result | Entrant       | Time
---|---------|--------|---------------|------
0  |   001   |    1   | Huw Edwards   | 54:78.491
1  |   001   |    2   | Fiona Bruce   | 0.250
2  |   001   |    3   | Sophie Raworth| 3.114
3  |   001   |    4   | George Alagiah| 6.756
4  |   001   |    5   | Mishal Husain | 42.452
5  |   001   |    6   | Peter Sissons | 58.004
6  |   002   |    1   | Fiona Bruce   | 43:08.549
7  |   002   |    2   | Sophie Raworth| 5.439
8  |   002   |    3   | Mishal Husain | 5.444
9  |   002   |    4   | Huw Edwards   | 6.007
10 |   002   |    5   | Peter Sissons | 13.000
11 |   002   |    6   | George Alagiah| 2:12.782

我试过这样的查询(没有按预期工作):

SELECT r.id, r.entrant, r.time 
FROM races as r 
LEFT JOIN races as r1 
ON r1.id > r.id 
WHERE r.result = "2"

我希望得到如下所示的结果，因为它显示了 Huw Edwards 享有的胜率。

id | race_id | entrant     | time
---|---------|-------------|-------
 1 |  001    | Huw Edwards | 0.250
 7 |  002    | Fiona Bruce | 5.439

这是我的表架构:

CREATE TABLE `races` (
 `series` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
 `year` int(11) NOT NULL,
 `round` int(11) NOT NULL,
 `date` date NOT NULL,
 `race_id` text COLLATE utf8_unicode_ci NOT NULL,
 `track` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
 `result` int(11) NOT NULL,
 `number` int(11) NOT NULL,
 `class` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
 `class_pos` int(11) NOT NULL,
 `runner` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
 `runner_id` int(11) NOT NULL,
 `runner2` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
 `runner_id2` int(11) NOT NULL,
 `entrant` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
 `laps` int(11) NOT NULL,
 `time` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
 `best` text COLLATE utf8_unicode_ci NOT NULL,
 `qual` int(11) NOT NULL,
 `marque` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
 `id` int(11) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43021 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 1

不是100%清楚你想要得到什么。

请提供更多示例以及其他一些预期结果。到目前为止，为了让您了解如何获取所需数据:

http://sqlfiddle.com/#!9/7eb44f/2

SELECT r.id,
      r.race_id,
       r.entrant,
       r1.entrant as Winner, 
       r1.time WinnerTime,
       r.time 
FROM races as r 
LEFT JOIN races as r1 
ON r1.result = 1 
   AND r1.race_id = r.race_id
WHERE r.result = 2

更新 您的表架构很糟糕。为了使我对您的表的查询更快，您至少应该更改 race_id 列类型整数(或 varchar)而不是文本!然后您需要在 result 和 race_id 列上创建索引:

ALTER TABLE races MODIFY race_id INTEGER; 

CREATE INDEX result_race_id ON races (result, race_id);