如何在sql中使用MAX进行分组

Question

比赛

RaceID	年份
01	2010
02	2011
03	2011
04	2011
05	2012
06	2012
07	2013

因此，您在公共字段(即race_id)上加入比赛和结果，然后只选择年份和速度，然后按年份分组，获得速度的最大值。

SELECT 
race.year
, MAX(results.speed) AS max_speed
FROM race 
JOIN results ON (race.raceid = results.raceid)
GROUP BY 
race.year

对于带有"max_Speed"、"recordist RaceID"、"ResultID"的"Year"：

select a.Year, a.max_Speed, b.RaceID, b.ResultID from
(select Year, max(Speed) max_Speed from Race a1 
left join Results a2 on a2.RaceID = a1.RaceID
group by Year
) a
left join
(select Year, Speed, b1.RaceID, ResultID from Race b1 
left join Results b2 on b2.RaceID = b1.RaceID) b
on b.Year = a.Year
and b.Speed = a.max_Speed
order by Year desc

仅适用于具有"最大速度"的"年份"：

select
year
, max(speed) max_speed
from race a
left join results b
on b.raceID = a.raceID
group by year
order by year desc

SQL Fiddle