如何在给定的表中找到每个主题的前三名

小编典典

如何在给定的表中找到每个主题的前三名

sql

id  - Name - Subject -  Marks
1   - ABC  - MAT    -  90
2   - ABC  - SCI    -  80
3   - ABC  - ENG    -  90
4   - ABC  - HIS    -  96
5   - ABC  - PHY    -  70
6   - ABC  - CHE    -  43
7   - XYZ  - MAT    -  90
8   - XYZ  - SCI    -  80
9   - XYZ  - ENG    -  90
10  - XYZ  - HIS    -  96
11  - XYZ  - PHY    -  70
13  - XYZ  - CHE    -  43

etc .....

只想显示每个主题的3个顶层

ABC - MATH - 90
XYZ - MATH - 90
DEF - MATH - 80
etc

阅读 188

2021-04-22

共1个答案

小编典典

您可以使用变量来执行此操作。

select t.*
from (select t.*,
             (@rn := if(@s = subject, @rn + 1,
                        if(@s := subject, 1, 1)
                       )
             ) as rn
      from t cross join
           (select @rn := 0, @s := '') params
      order by subject, marks desc
     ) t 
where rn <= 3
order by t.subject, t.rn;

2021-04-22