SQL:如何显示不同年份的top值

Question

我有一个表格，里面是不同年份的足球比赛比分，我一直在努力找出哪支球队每年的主场战绩最好，以及他们在主场赢了多少场比赛。我尝试了以下代码:

hometeam,
season,
count(case when FTR = 'H' then 'hw' END) AS 'homewins'
from results
group by HomeTeam, Season
order by homewins desc,  season```
but this does not give me the name of the team with the best record each eay, but instead the best records of all time. any help isa ppreciated (I am still very much a beginner).
(the seasons continue further down)[Source table attached][1]:

[1]: https://i.stack.imgur.com/hOlMG.png

Answer 1

这是一种简单的方法，将结果按年份排序，并将最高的主场获胜数放在首位。不确定这是不是你要找的

DROP TABLE IF EXISTS #tmp
CREATE TABLE #tmp (Season NVARCHAR(5), HomeTeam NVARCHAR(20), FTR NVARCHAR(1))
INSERT INTO #tmp VALUES ('2019','TeamA','H'),('2019','TeamA','H'),('2019','TeamA','H'),
('2019','TeamB','A'),('2019','TeamB','H'),('2019','TeamB','H'),
('2020','TeamA','H'),('2020','TeamA','A'),('2020','TeamA','A'),
('2020','TeamB','H'),('2020','TeamB','H'),('2020','TeamB','H') 
;WITH HomeWins
AS (
SELECT Season,HomeTeam,CASE WHEN FTR='H' THEN 1 ELSE 0 END AS Wins
FROM #tmp
)
SELECT Season,HomeTeam,SUM(Wins) AS HomeWins
FROM HomeWins
GROUP BY Season,HomeTeam
ORDER BY Season ASC,SUM(Wins) DESC