学生考试, 一年考试N次, 取各学科平均分排名前10的学生 select a.* from
(
select studentid, avg(score) as score1, subject from tableA
group by studentid , subject
) a
where
(
select count(1) from (
select studentid, avg(score) as score1, subject from tableA
group by studentid , subject )b
where a.studentid = b.studentid and a.subject = b.subject and b.score1> a.score1) < 10
)
order by a.subject , a.score1 desc
(
select studentid, avg(score) as score1, subject from tableA
group by studentid , subject
) a
where
(
select count(1) from (
select studentid, avg(score) as score1, subject from tableA
group by studentid , subject )b
where a.studentid = b.studentid and a.subject = b.subject and b.score1> a.score1) < 10
)
order by a.subject , a.score1 desc
where score in
(
select top 10 score from tb where subject=t.subject order by score desc
)
order by subject , score desc
select t.* from
(
select subject , studentid , avg(score) score from tb group by subject , studentid
) tb t
where score in
(
select top 10 score from (select subject , studentid , avg(score) score from tb group by subject , studentid) m where subject=t.subject order by score desc
)
order by subject , score desc
from (select rownum ,subject , studentid , avg(score) score from tb group by subject , studentid order by score)
where rownum <= 10