SELECT year, country, suicides_per_100k_pop
FROM (
SELECT year, country, suicides_per_100k_pop,
DENSE_RANK() OVER (PARTITION BY year ORDER BY suicides_per_100k_pop DESC) AS rank
FROM suicides
WHERE year BETWEEN 1985 AND 2016
) t
WHERE rank <= 3
ORDER BY year, suicides_per_100k_pop DESC
说明:
DENSE_RANK()
来为每个年份内的国家按照自杀率从高到低排名,然后返回每个国家的排名;