常见场景下的SQL运用

记录一些常见需求场景下的SQL表达式。

假如有这么一个游戏表game，它有这么四个字段id、user_id、score、created_time，记录的是用户玩某个游戏的得分记录，每个人可以玩多次，游戏只有一款。

假如我们要做一个排行榜，榜单上只展示最高的10个得分，这就很简单：

select score from game order by score desc limit 10

假如我们要做的排行榜，要展示Top 10的得分的同时，还要展示出对应的用户ID，用户ID不重复，比如一个用户玩了三次，得分按理说都上榜，也只取他得分最高的那次，这就有些复杂了：

select user_id,max(score) as max_score from game group by user_id order by max_score desc limit 10

假如需求同上，只是额外要求榜单上还要展示用户玩得该得分的时间戳，也就是created_time，那就变得复杂多了，此时需要用到窗口函数row_number()，SQL如下：

select * from (
  select
    id, user_id, score, ROW_NUMBER() over(PARTITION BY user_id order by score desc) as rid
  from
    game
  group by id,user_id,score
) t where rid=1 order by score desc limit 10

如果这个榜单有点变态，每个用户最多可以上榜三次，而不是只在榜单上出现一次，使用上面的解法就很简单，只需要将rid=1改成rid<=3即可。

假如需求完全变了，是需要统计每个参与者的top 3的得分记录，榜单先按用户ID逆序排，然后每个用户的top 3得分再逆序排：

select * from (
  select
    id, user_id, score, ROW_NUMBER() over(PARTITION BY user_id order by score desc) as rid
  from
    game
  group by id,user_id,score
) t where rid<=3 order by user_id desc,rid