比賽
| RaceID | 年份01 | 2010 |
|---|
結果
| ResultID | RaceID速度
|---|
| A |
我怎樣才能得到每一年的最大速度，并按年份GROUP它呢？
uj5u.com熱心網友回復：
因此，你在共同的欄位即race_id上加入比賽和結果，然后只挑選年份和速度，然后你按年份分組并獲得最大速度。
SELECT
比賽.年份
, MAX(results.speed) AS max_speed
FROM比賽
JOIN results ON (race.raceid = results.raceid)
GROUP BY (race.raceid)
賽事.年份
uj5u.com熱心網友回復：
對于帶有max_Speed & recordist RaceID & ResultID的年份：
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
leftjoin
(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
;
僅針對Year & max_Speed:
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
leftjoin
(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
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/309804.html
標籤：
上一篇：如何用Sqlite制作多個資料庫