Sql 用于获取每个级别的最后 3 行 - Sql for get last 3 rows for each level

Sql for get last 3 rows for each level

本文关键字：最后用于获取 Sql | 更新日期: 2023-09-27

>我有一个问题。所以，我的表：

id      level      score
1        1          2
2        1          4
3        2          1
4        3          2
5        4          10
.......................

我想为每个级别最后 3 行按分数说明排序。我试过这样：

select * from table order by score desc group by level LIMIT 3

但不是工作。你能帮我吗？提前感谢，对不起我的英语

有几种技术可以做到这一点，一种方法是使用用户定义的变量作为

select id,`level`,score 
from (
  select *, 
  @rn:= if(@prev_level = `level`,@rn+1,1) as rn,
  @prev_level:= `level`
  from mytable,(select @rn:=0,@prev_level:=0)r
  order by `level`,score desc
)r
where r.rn <=3;

演示

SQL 解决方案：

select * from table t1
WHERE 
        (
            SELECT  COUNT(*) 
            FROM    table  t
            WHERE t.level = t1.level AND 
                  t.score >= t1.score
        ) <= 3 order by t1.level, t1.score desc

SqlFiddle