排名问题

来源：互联网发布：字符数组转换成字符串编辑：程序博客网时间：2024/05/01 21:18

-- Create table
create table STUDENT
(
ID    VARCHAR2(10),
XM    VARCHAR2(10),
SCORE NUMBER(10)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);

select id, score, rn
from (select id, score, rank() over(order by score) rn from student )
where rn < 10 // 相同的名次不并列跳过排名

select id, score, rn
   from (select id,
                score,
                dense_rank() over(/*partition by */ order by score) rn
           from student)
where rn < 10 // 相同的名次并列不跳过排名

   select *
            from (select id,
                         score,
                         row_number() over(order by score desc) rn
                    from student)
           where rn <= 10   // 不排名啊大哥名次相同的不排名

delete from student
where xm in
       (select xm from where student group by xm having count(xm) > 1)

   and id not in
       (select min(id) from where student group by xm having count(xm) > 1) // 删除排名相同的人只留一个

rowid 的意义大家可以百度。

0 0