insert tb(考号,姓名,班级,总分,考试名称)
select '1','aaa','c1',100,'bbb' union all
select '2','bbb','c1',50,'bbb' union all
select '3','cccc','b',100,'bbb' union all
select '4','bbb','c1',50,'ccc' union all
select '5','aaa','c1',100,'ccc' union all
select '5','cccc','b',50,'ccc'
--某班某次考试总分前100名有多少人
select count(*) from (
select 考号,姓名,班级,总分,考试名称,sorId=(select count(distinct 总分) from tb where a.考试名称=考试名称 and a.总分<=总分)
from tb a ) c
where 考试名称='bbb' and 班级='c1' and sorId<=200
--查询某班3次考试总分前100名各有多少人(刚才每班只考试三次。如果多于三次,请加班级OR)
select count(*) from (
select 考号,姓名,班级,总分,考试名称,sorId=(select count(distinct 总分) from tb where a.考试名称=考试名称 and a.总分<=总分)
from tb a ) c
where 班级='c1' and sorId<=200