mysql - 子子查询无法访问在外部范围内定义的表

Question

表 Races 包含列 Time、Racetrack 和 Racer。

表 Racetracks 包含列 Name 和 Length。

以下查询执行复杂的选择以查找 Racetracks 表(下部)的单行，然后计算该赛道的某些属性(上部)。其中一个属性是这里的平均比赛时间。

SELECT
    (select avg(Time) from Races where Racetrack = t.Name) as AverageTime
    /* more lines like the one above to calculate information about this track */
FROM (select * from Racetracks
      /* complicated where clauses to select a particular racetrack */
      limit 1
     ) as t;

我想稍微修改一下查询。而不是所有比赛的平均比赛时间，应该平均每个赛车手的最佳时间。这是我尝试过的:

SELECT
    (select avg(BestTime) from (select min(Time) as BestTime from Races where Racetrack = t.Name group by Racer) as b) as AverageTime
    /* more lines like the one above to calculate information about this track */
FROM (select * from Racetracks
      /* complicated where clauses to select a particular racetrack */
      limit 1
     ) as t;

尽管 MySQL 抛出了以下错误:

ERROR 1054 (42S22): Unknown column 't.Name' in 'where clause'

这似乎是相关的https://dba.stackexchange.com/questions/126339/subquery-cant-find-column-from-superquerys-join但我不知道如何以有效的方式重写我的查询。

Answer 1

没有简单的方法可以做到这一点。您可以预先计算所有赛道的值:

SELECT (select avg(BestTime)
        from (select RaceTrack, Racer, min(Time) as BestTime
              from Races 
              group by RaceTrack, Racer
             ) r
        where r.Racetrack = t.Name 
       ) as AverageTime
    /* more lines like the one above to calculate information about this track */
FROM (select * from Racetracks
      /* complicated where clauses to select a particular racetrack */
      limit 1
     ) as t;

对于一条赛道，逻辑是子查询还是在 from 子句中并不重要。如果您同时进行多个赛道，我建议您在 from 子句中进行预先汇总。