比如这样的场景:表 exam 记录学生每次测验的成绩,stu_id 是学生id,score 是成绩。查询出全部学生的最好的两次测验成绩的sql要怎么写?
比如表 exam 的数据如下,1 98
1 96
2 100
1 85
2 89
2 90
3 82
3 66
3 75查询到结果应该是
1 98
1 96
2 100
2 90
3 82
3 75
比如表 exam 的数据如下,1 98
1 96
2 100
1 85
2 89
2 90
3 82
3 66
3 75查询到结果应该是
1 98
1 96
2 100
2 90
3 82
3 75
SELECT * from tty a where 2>(select count(*) from tty where a.id=id and a.score<score)
group by id
having count(id)=2
limit 2;
[征集]分组取最大N条记录方法征集,及散分....
from Table1 a
where id in (select id from Table1 where ClsNo=a.ClsNo order by Score desc limit 2)
order by a.ClsNo,a.Score desc
from exam a
where id in (select id from exam where ClsNo=a.ClsNo order by Score desc limit 2)
order by a.ClsNo,a.Score desc
select id, max(score) from exam
group by id
having count(id)=2
limit 2; 这个不错