在sql中查找每年奖牌最多的运动员的查询

SELECT year, athlete_id, COUNT(*) AS medal_count
FROM medals
GROUP BY year, athlete_id
HAVING COUNT(*) = (
    SELECT MAX(medal_count)
    FROM (
        SELECT year, athlete_id, COUNT(*) AS medal_count
        FROM medals
        GROUP BY year, athlete_id
    ) AS medal_counts
    WHERE medal_counts.year = medals.year
)

SELECT a.year, a.athlete_id, b.athlete_name, a.medal_count
FROM (
    SELECT year, athlete_id, COUNT(*) AS medal_count
    FROM medals
    GROUP BY year, athlete_id
    HAVING COUNT(*) = (
        SELECT MAX(medal_count)
        FROM (
            SELECT year, athlete_id, COUNT(*) AS medal_count
            FROM medals
            GROUP BY year, athlete_id
        ) AS medal_counts
        WHERE medal_counts.year = medals.year
    )
) AS a
JOIN athletes AS b ON a.athlete_id = b.athlete_id