select * from exam where student_id in (select top 6 student_id from exam where class_id = '1' order by score desc)
select * from exam where student_id in (select top 6 student_id from exam t1 where t1.class_id =exam.classid and exam.student_id=t1.student_id order by score desc)
试试: select s.class_id,s.student_id,s.score from exam s join exam t on (s.class_id=t.class_id) where s.score<=t.score group by s.class_id,s.student_id,s.score having count(*)<=6 order by 1,3 desc
select * from exam where student_id in (select top 6 t1.student_id from exam t1 where t1.class_id =exam.classid and exam.student_id=t1.student_id order by score desc)
select * from exam a where student_id in ( select top 6 student_id from exam where class_id=a.class_id order by score desc )
from exam
where student_id in (select top 6 student_id from exam where class_id = '1' order by score desc)
from exam
where student_id in
(select top 6 student_id from exam t1 where t1.class_id =exam.classid and exam.student_id=t1.student_id
order by score desc)
select s.class_id,s.student_id,s.score
from exam s join exam t on (s.class_id=t.class_id)
where s.score<=t.score
group by s.class_id,s.student_id,s.score
having count(*)<=6
order by 1,3 desc
from exam
where student_id in
(select top 6 t1.student_id from exam t1 where t1.class_id =exam.classid and exam.student_id=t1.student_id
order by score desc)
where student_id in
(
select top 6 student_id from exam where class_id=a.class_id
order by score desc
)
我多了一个条件:
and exam.student_id=t1.student_id