如何在给定的表格中找到每个主题的前三名 [英] How to find top 3 topper of each subject in given table

问题描述

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首

Just want to show 3 topper of each subject

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

推荐答案

您可以使用变量进行此操作.

You can do this using variables.

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;

这篇关于如何在给定的表格中找到每个主题的前三名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！