select * from ( select 学号,班级,姓名,sum(1) over(order by 语文成绩 desc) as 语文成绩排名, ,sum(1) over(order by 数学成绩) as 数学成绩排名, from select 学号,班级,姓名,MAX(decode(学科,'语文',成绩,0)) as 语文成绩, MAX(decode(学科,'语文',成绩,0)) as 数学成绩 from tab1 group by 学号,班级,姓名 ) tmp ) where 语文成绩排名 <=10 and 数学成绩排名>10
select *
from (
select 学号,班级,姓名,sum(1) over(order by 语文成绩 desc) as 语文成绩排名,
,sum(1) over(order by 数学成绩) as 数学成绩排名,
from
select 学号,班级,姓名,MAX(decode(学科,'语文',成绩,0)) as 语文成绩,
MAX(decode(学科,'语文',成绩,0)) as 数学成绩
from tab1
group by 学号,班级,姓名
) tmp
) where 语文成绩排名 <=10 and 数学成绩排名>10