WITH
places AS (
SELECT row_number() OVER (ORDER BY score DESC, name) AS place, name, score
FROM players
JOIN results ON players.id = results.player_id
)
SELECT first_value(place) OVER (PARTITION BY score ORDER BY place) AS place, name, score
FROM places ORDER BY score DESC, name;
select rank() over (order by r.score desc) as order1,
dense_rank() over (order by r.score desc) as order2,
p.name,
r.score
from players p
join results r
on p.id = r.player_id
order by r.score desc, p.name;
2条答案
按热度按时间nfs0ujit1#
下面是一个可能的解决方案:
请参阅fiddle。
aiqt4smr2#
如果出现并列,则需要rank()来保持排名。如果不需要在排名之间设置间隔,则需要dense_rank()。
结果(包括rank()和dense_rank()以供参考)。