sql 前十名

[ghostr_ider_lsj]

oracle sql如何获取班级成绩前十的，可能出现有排名相同的情况，比如第二名有两个，第三名有四个这样的情况；
table_name: student;
column: id 标识
score 分数

[jimmylin040]

  select  sum(1) over(order by score desc) seq,
          code,score
    from
    (  
select '1001' as code,80 as score
  from dual
union all
select '1002',81
  from dual
union all
select '1003',79
  from dual
union all
select '1004',80
  from dual
union all
select '1005',95
  from dual
union all
select '1006',84
  from dual
union all
select '1007',56
  from dual
union all
select '1008',95
  from dual
union all
select '1009',90
  from dual
union all
select '1010',75
  from dual
union all
select '1011',60
  from dual
union all
select '1012',74
  from dual
union all
select '1013',81
  from dual
  )

[delphisanding1]

[Quote=引用 2 楼 liuyyuns 的回复:]
select id,score from(select id,score,rownum n from student order by core desc) where n <=10;
[/Quote]

要给你的视图加个别名

[hebo2005]

用分析函数
row_number() over (
partition by 分组条件 order by 排序条件
) 没有并列，顺序排列
rank 有并列，并跳过下一排名，例如有2个并列第一，下面就是第三名
dense_rank 有并列并且不跳过下一排名，如两个第一，第二有3个，就会显示2个1，3个有2
有关分析函数用法，搜索下

[tangren]

SELECT *
  FROM (SELECT id, scroe, dense_rank() over(ORDER BY score) rn FROM student)
 WHERE rn <= 10;

[304的的哥]

SQL> with t as(
  2       select '21001' id,'杨过' sname,'Oracle' c_name,90 score from dual union all
  3       select '20110','张三丰','Oracle',90 from dual union all
  4       select '12350','王五','Oracle',88 from dual union all
  5       select '14010','李想','Oracle',69 from dual union all
  6       select '10012','赵六','C++',87 from dual)
  7  select id,sname,c_name,
  8         rank() over (partition by c_name order by score desc) rank_1,
  9         dense_rank() over (partition by c_name order by score desc) rank_2
 10  from t
 11  where c_name='Oracle'
 12  /

ID    SNAME  C_NAME     RANK_1     RANK_2
----- ------ ------ ---------- ----------
21001 杨过   Oracle          1          1
20110 张三丰 Oracle          1          1
12350 王五   Oracle          3          2
14010 李想   Oracle          4          3
/*
下面的两个函数都能排序，但是有点差别：
rank()：出现排名相同的时候，就将排名中的位置留下来
dense_rank():它不将相同位置的名额留下来，而是在下一个排名时使用，
从上面的实例中我们已经看出来了
*/

[liuyyuns]

你是想查询前十名同学(只有10个)，还是想查询前十名的同学(有可能大于10)

[liuyyuns]

select id,score from(select id,score,rownum n from student order by core desc) where n <=10;

[Sunkien]

1、按分数distinct一下，找出第十名分数，再找出大于这个分数的人

[tiantina]

select rownum,ename,sal from (select * from emp order by sal desc) where rownum<=10;

[ghostr_ider_lsj]

谢谢各位，问题已解决，我是按8楼得那种方法。