数据表如下:
有并列分组查询结果:
select *
from student t1
where t1.score=(
select t2.score
from student t2
where t1.class=t2.class
order by t2.score desc
limit 1,1
)
查询结果:(注意查询结果应该是依据 t2的分数排序)
无并列分组查询结果:
添加rank 排名,作为排序标识
select *
from (select * ,row_number() over (order by class,score desc) ranking
from student) t1
where t1.ranking =(
select ranking
from (select * ,row_number() over (order by class,score desc) ranking
from student) t2
where t1.class=t2.class
limit 1,1
)