排名函数



drop table t;
create table t (stuid  varchar(10),
                classname varchar(50),
                score int);              
insert into t  values ('A0001','cn',60);
insert into t  values ('A0002','cn',80);
insert into t  values ('A0003','cn',80);
insert into t  values ('A0004','cn',90);
insert into t  values ('A0005','eg',70);
insert into t  values ('A0006','eg',99);
insert into t  values ('A0007','ma',67);
insert into t  values ('A0008','ma',77);
insert into t  values ('A0009','ma',78);
insert into t  values ('A0010','ma',88);
insert into t  values ('A0011','cn',11);
insert into t  values ('A0011','cn',81);
select * from t ;
select * from t  WHERE ROW_NUMBER()<3;
select classname,score,dense_rank() over( partition by classname order by score)  from t;
select classname,score,rank() over(partition by classname order by score)  from t;
select classname,score,row_number() over(partition by classname order by score)  from t;
select classname,score,ntile(2) over(partition by classname order by score desc)  from t;

select * from test order by score;
select * from test order by (case when score IS null  then 0 else 1 end ) ASC,SCORE DESC;

----倒数，正第一
select * from t  where classname='cn' ;
select stuid
from t
where classname='cn'

select(select top 1 stuid firstn from t
where classname='cn'
order by score) f,

(select top 1 stuid lastn from t
where classname='cn'
order by score desc) l