最大/最小/计数等没有给出好的价值

I have a f1 database from ergast.com. I've tried to get best laptime and number of pitstops for each driver in each race, so I've written sql query like this:

我有一个来自ergast.com的f1数据库。我试图在每场比赛中为每个车手获得最佳的laptime和pitstops数量，所以我写了这样的sql查询：

SELECT results.driverId, results.constructorId, races.circuitId, races."year"-1949,
       results.statusId, results.positionOrder, results.grid, results.points, 
       results.laps, ISNULL(max(pitstops.stop), 0), min(laptimes.milliseconds), 
       results.fastestLapTime, drivers.forename, drivers.surname, circuits.name
FROM results 
INNER JOIN races 
   ON (results.raceId = races.raceId)
LEFT OUTER JOIN pitstops 
   ON (races.raceId = pitstops.raceId)
LEFT OUTER JOIN laptimes 
   ON (laptimes.raceId = races.raceId)
INNER JOIN drivers 
   ON (drivers.driverId = results.driverId)
INNER JOIN circuits 
   ON (circuits.circuitId = races.circuitId)
WHERE ((races."year"=2011) OR (races."year"=2012))
  AND ((races.circuitId = 1) OR (races.circuitId = 2))
GROUP BY results.driverId, constructorId, races.circuitId, races."year"-1949,
         statusId, positionOrder, grid, points, laps, fastestLapTime, forename,
         surname, circuits.name

Unfortunately this gives me the same best laptime and number of pitstops for every driver in given race. How can I fix it? Should I add something to group by?

不幸的是，在给定的比赛中，这给了我同样最好的laptime和每个车手的进站数量。我该如何解决？我应该添加一些东西到分组吗？

3 个解决方案

#1

You are joining pitstops on raceId only, thus getting the maximum pitstop per race. But you say "and number of pitstops for every driver in given race". This indicates that a pitstop should be related to a driver, e.g.:

你只在raceId上加入了pitstops，从而获得了每场比赛的最大进站。但是你说“在给定的比赛中每个车手的进站次数”。这表明一个pitstop应该与一个驱动程序有关，例如：

LEFT OUTER JOIN pitstops 
   ON (results.raceId = pitstops.raceId and results.driverId = pitstops.driverId)

The same should be true for laptimes.

对于laptimes也应如此。

(There may be more errors in your query. Please qualify all columns with a table name, if you want more help, so we see which columns is in which table. Even better would be table definitions.)

（您的查询中可能存在更多错误。如果您需要更多帮助，请使用表名限定所有列，因此我们会看到哪些列位于哪个表中。更好的是表定义。）

#2

And here is yet another "answer" :-)

这是另一个“答案”:-)

You are using technical IDs (raceId, driverId, etc.) and you are surprised you have to join on more than one column. This indeed is somewhat untypical for a database that is based on technical IDs. So let's check why this is...

您正在使用技术ID（raceId，driverId等），您很惊讶必须加入多个专栏。对于基于技术ID的数据库，这确实有点不典型。那么让我们来看看为什么......

There should be a table that lists the drivers per race. Let's call it participation:

应该有一张表格列出了每场比赛的车手。我们称之为参与：

participation(participationid, raceid, driverid)

With such a table, your other tables would no longer contain the combination of raceid and driverid, but only the participationid:

使用这样的表，您的其他表将不再包含raceid和driverid的组合，但只包含参与者：

pitstops(pitstopid, participationid, stop, ...)

laptimes(laptimeid, participationid, milliseconds, ...)

results(resultid, participationid, points, ...)

And you'd join accordingly on that one column only:

而且你只会加入一个专栏：

LEFT OUTER JOIN pitstops 
  ON pitstops.participationid = results.participationid

I take it, however, that there can be just one result record per raceid and driverid? Then your participation table is the results table really (only with a worse name :-). You should hence have a resultid in the table and the other tables referring to it:

但是，我认为每个raceid和driverid只能有一个结果记录？然后你的参与表真的是结果表（只有更糟的名字:-)。因此，您应该在表中进行重新分析，并在其他表中引用它：

pitstops(pitstopid, resultid, stop, ...)

laptimes(laptimeid, resultid, milliseconds, ...)

And you'd join accordingly on that one column only:

而且你只会加入一个专栏：

LEFT OUTER JOIN pitstops 
  ON pitstops.resultid = results.resultid

#3

Adding to the answer already given: You can simplify your query by getting the aggregates in subqueries (either in the FROM clause or in the SELECT clause). E.g.:

添加已经给出的答案：您可以通过在子查询中获取聚合来简化查询（在FROM子句或SELECT子句中）。例如。：

select 
  results.driverid,
  results.constructorid,
  races.circuitid,
  races."year" - 1949,
  results.statusid,
  results.positionorder,
  results.grid,
  results.points,
  results.laps,
  (
    select coalesce(max(stop), 0)
    from pitstops
    where pitstops.raceid = results.raceid
    and pitstops.driverid = results.driverid
  ) as max_pitstops,
  (
    select coalesce(min(milliseconds), 0)
    from laptimes
    where laptimes.raceid = results.raceid
    and laptimes.driverid = results.driverid
  ) as min_laptimes,
  results.fastestlaptime,
  drivers.forename,
  drivers.surname,
  circuits.name
from results
join drivers on drivers.driverid = results.driverid
join races on races.raceid = results.raceid
join circuits on circuits.circuitid = races.circuitid
where races."year" in (2011, 2012)
and races.circuitid in (1, 2);

or

要么

select 
  results.driverid,
  results.constructorid,
  races.circuitid,
  races."year" - 1949,
  results.statusid,
  results.positionorder,
  results.grid,
  results.points,
  results.laps,
  coalesce(pits.max_stop, 0) as max_pitstops,
  coalesce(laps.min_milliseconds, 0) as min_laptimes,
  results.fastestlaptime,
  drivers.forename,
  drivers.surname,
  circuits.name
from results
join drivers on drivers.driverid = results.driverid
join races on races.raceid = results.raceid
join circuits on circuits.circuitid = races.circuitid
left join
(
  select raceid, driverid, max(stop) as max_stop
  from pitstops
  group by raceid, driverid
) pits on pits.raceid = results.raceid and pits.driverid = results.driverid
left join
(
  select raceid, driverid, min(milliseconds) as min_milliseconds
  from laptimes
  group by raceid, driverid
) laps on laps.raceid = results.raceid and laps.driverid = results.driverid
where races."year" in (2011, 2012)
and races.circuitid in (1, 2);