排名问题

-- 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  //  相同的名次不并列  跳过排名

   id score rn
1 2   21      1
2 9   34       2
3 6   66       3
4 11  67     4
5 13  76     5
6 10   90     6
7 7     90       6
8 8     90       6
9 1    123     9

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

1 2 21       1
2 9 34       2
3 6 66       3
4 11 67    4
5 13 76     5
6 10 90     6
7 7 90        6
8 8 90        6
9 1 123      7
10 4 212   8
   select *
            from (select id,
                         score,
                         row_number() over(order by score desc) rn
                    from student)
           where rn <= 10   // 不排名啊大哥  名次相同的不排名
    id score rn

1 12 567   1
2 5 234     2
3 3 231     3
4 4 212     4
5 1 123     5
6 10 90     6
7 8 90       7
8 7 90       8
9 13 76     9
10 11 67   10

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

 

rowid  的意义大家可以百度。