select * from (select name ,class ,score ,row_number() over(partition by class order by score desc nulls last) rn from emp )t where rn<=2 order by class,score ;
懂得用排名函数row_number() 就可以解决
支持一楼! Oracle分析函数很好解决你的需求 select * from (select name ,class ,score ,row_number() over(partition by class order by score desc nulls last) rn from emp )t where rn<=2 order by class,score desc ;
正解在此:select name,class,score from(select name,class,score,rank() over(partition by class order by score desc)t from emp) where t<3;
----sinpoal ----table----------- create table emp( name varchar(10), class varchar(10), score number )---------sql statment--------- select p.name,p.score,p.class from ( select row_number() over(partition by class order by score desc) no,c.* from emp c order by class ,score desc) p where p.no in(1,2) /*----------------result---------- 1 lucy 80 chinese 2 tom 76 chinese 3 lucy 85 english 4 jeey 81 english */
哦。。说错了 5、6的问题是一样的,用这个合适: select name,class,score from(select name,class,score,dense_rank() over(partition by class order by score desc)t from emp) where t<3;
select name,class,score from (select name,class,score, dense_rank() over(partition by class order by score desc) rn from emp) a where a.rn<3
(select name
,class
,score
,row_number() over(partition by class order by score desc nulls last) rn
from emp
)t
where rn<=2
order by class,score
;
就可以解决
Oracle分析函数很好解决你的需求
select * from
(select
name
,class
,score
,row_number() over(partition by class order by score desc nulls last) rn
from emp
)t
where rn<=2
order by class,score desc
;
----sinpoal
----table-----------
create table emp(
name varchar(10),
class varchar(10),
score number )---------sql statment---------
select p.name,p.score,p.class from (
select
row_number() over(partition by class order by score desc) no,c.* from emp c
order by class ,score desc) p where p.no in(1,2)
/*----------------result----------
1 lucy 80 chinese
2 tom 76 chinese
3 lucy 85 english
4 jeey 81 english
*/
select name,class,score from(select name,class,score,dense_rank() over(partition by class order by score desc)t from emp) where t<3;
select name,class,score
from (select name,class,score,
dense_rank() over(partition by class order by score desc) rn
from emp) a
where a.rn<3