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

Question

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

Answer 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;

Answer 2

select m1.id, max(m1.marks) 'marks', max(m1.subject) 'subject'
from      marks m1 
left join marks m2 on m2.subject =m1.subject and ( m2.marks>=m1.marks )
left join marks m3 on (m3.subject =m2.subject) and m3.id<>m1.id and m3.id<>m2.id and m3.marks>=m2.marks
left join marks m4 on (m4.subject =m3.subject) and m4.id<>m1.Id and m4.id<>m2.id and m4.id <>m3.id and m4.marks>=m3.marks
left join marks m12 on (m12.subject =m1.subject and m12.subject =m2.subject ) and m12.marks>m1.marks and m12.marks<m2.marks 
left join marks m23 on (m23.subject=m2.subject and m23.subject=m3.subject) and ( m23.marks>m2.marks and m23.marks < m3.marks)
where 
    (m4.id is null)
and m12.id is null
and m23.id is null
group by m1.id
order by max(m1.subject) asc, max(m1.marks) desc