精华内容
下载资源
问答
  • 主要介绍了Oracle排名函数(Rank)实例详解,需要的朋友可以参考下
  • Oracle排名函数

    千次阅读 2016-10-27 10:53:17
    select * from chenhf_20161027_01; select a.*,rownum from chenhf_20161027_01 a order by 个人年度最高分 rownum在order by之前...row_number()函数 相同的值会出现排名递增的情况; select a.*,row_number()ove

    create table course_score(
           course_name varchar2(4),-- 科目
           user_name varchar2(4),-- 姓名
           score number-- 成绩
    );

    插入的数据如下:

    科目 姓名 成绩

    数学 陈大 60
    语文 陈大 48
    英语 陈大 60
    数学 王二 60
    语文 王二 74
    英语 王二 70
    数学 张三 50
    语文 张三 50
    英语 张三 70
    数学 李四 30
    语文 李四 80
    英语 李四 63

    共12行数据;

    select a.*,rownum from course_score a;

    select a.*,rownum from course_score a order by score desc;

    在order by之前rownum已经确定,order by无法改变排名(排名顺序都不能保证,就更没必要讨论相同值排名不同了); 

    row_number()函数

    select a.*,row_number()over(order by score desc) 排名 from course_score a;

    加partition()分区,分科目排名

    相同值的排名会递增(相同成绩需要相同)

     rank()函数

    select a.*,rank()over(order by score desc) 排名 from course_score a;

    加partition()

    select a.*,rank()over(partition by course_name order by score desc) 排名 from course_score a;

    rank()可以达到相同值排名相同,但排名相同的相当于会占位,后面的排名会跳号

    dense_rank()

    select a.*,dense_rank()over(order by score desc) 排名 from course_score a;

    加partition()

    select a.*,dense_rank()over(partition by course_name order by score desc) 排名 from course_score a;

    dense_rank()可实现并列排名;

     

    具体用row_number()、rank()、dense_rank()根据自己的实际情况进行选择;

     

     

     

     

     

    展开全文
  • oracle中,有rank,dense_rank,row_number,以及分组排名partition。 说明: rank:排名会出现并列第n名,它之后的会跳过空出的名次,例如:1,2,2,4 dense_rank:排名会出现并列第n名,它之后的名次为n+1,例如:1,2,...
  • row_number,rank(),dense_rank() ,这三个函数必须要用over子句选择对某...rank函数用于返回结果集的分区内每行的排名,行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名,与row_num...

    row_number,rank(),dense_rank() ,这三个函数必须要用over子句选择对某一列进行排序才能生成序号。

    row_number的用途非常广泛,排序最好用它,它会为查询出来的每一行记录生成一个序号,依次排序且不会重复,注意使用

    rank函数用于返回结果集的分区内每行的排名,行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名,与row_number函数不同的是,rank函数考虑到了over子句中排序字段值相同的情况,如果使用rank函数来生成序号,over子句中排序字段值相同的序号是一样的,后面字段值不相同的序号将跳过相同的排名号排下一个,也就是相关行之前的排名数加一,可以理解为根据当前的记录数生成序号,后面的记录依此类推。

    dense_rank函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时,将不跳过相同排名号,rank值紧接上一次的rank值。在各个分组内,rank()是跳跃排序,有两个第一名时接下来就是第三名,dense_rank()是连续排序,有两个第一名时仍然跟着第二名。

    借助实例能更直观地理解:

    假设现在有一张学生表student,学生表中有姓名、分数、课程编号。

    select * from student;

    现在需要按照课程对学生的成绩进行排序:

    --row_number() 顺序排序
    select name,course,row_number() over(partition by course order by score desc) rank from student;

    --rank() 跳跃排序,如果有两个第一级别时,接下来是第三级别
    select name,course,rank() over(partition by course order by score desc) rank from student;

     

    --dense_rank() 连续排序,如果有两个第一级别时,接下来是第二级别 
    select name,course,dense_rank() over(partition by course order by score desc) rank from student;

    取得每门课程的第一名:

    --每门课程第一名只取一个: 
    select * from (select name,course,row_number() over(partition by course order by score desc) rank from student) where rank=1;
    --每门课程第一名取所有: 
    select * from (select name,course,dense_rank() over(partition by course order by score desc) rank from student) where rank=1;
    --每门课程第一名取所有:
    select * from (select name,course,rank() over(partition by course order by score desc) rank from student) where rank=1;

    附:每门课程第一名取所有的其他方法(使用group by 而不是partition by):

    select s.* from student s
      inner join(select course,max(score) as score from student group by course) c
      on s.course=c.course and s.score=c.score; 
    --或者使用using关键字简化连接
    select * from student s
      inner join(select course,max(score) as score from student group by course) c
      using(course,score);

     

    关于Parttion by:

      Parttion by关键字是Oracle中分析性函数的一部分,用于给结果集进行分区。它和聚合函数Group by不同的地方在于它只是将原始数据进行名次排列,能够返回一个分组中的多条记录(记录数不变),而Group by是对原始数据进行聚合统计,一般只有一条反映统计值的结果(每组返回一条)。

      TIPS:

      使用rank over()的时候,空值是最大的,如果排序字段为null, 可能造成null字段排在最前面,影响排序结果。

      可以这样: rank over(partition by course order by score desc nulls last)

    总结:

      在使用排名函数的时候需要注意以下三点:

      1、排名函数必须有 OVER 子句。

      2、排名函数必须有包含 ORDER BY 的 OVER 子句。

      3、分组内从1开始排序。

     

     

     

     

     

     

     

     

    展开全文
  • --已知:两种排名方式(分区和不分区):使用和不使用partition --两种计算方式(连续,不连续),对应函数:dense_rank,rank   ·查询原始数据:学号,姓名,科目名,成绩 select * from t_score S_ID S_NAME SUB...

    --已知:两种排名方式(分区和不分区):使用和不使用partition

    --两种计算方式(连续,不连续),对应函数:dense_rank,rank

     

    ·查询原始数据:学号,姓名,科目名,成绩

    select * from t_score

    S_ID

    S_NAME

    SUB_NAME

    SCORE

    1

    张三                  

    语文                             

    80.00                 

    2

    李四   

    数学

    80.00

    1

    张三   

    数学

    0.00

    2

    李四   

    语文

    50.00

    3

    张三丰 

    语文

    10.00

    3

    张三丰 

    数学

     

    3

    张三丰 

    体育

    120.00

    4

    杨过   

    Java

    90.00

    5

    mike   

    c++

    80.00

    3

    张三丰 

    Oracle

    0.00

    4

    杨过   

    oracle

    77.00

    2

    李四   

    Oracle

    77.00

     

    ·查询各学生科目为Oracle排名(简单排名)

    select sc.s_id,sc.s_name,sub_name,sc.score,

     rank() over (order by score desc名次

    from t_score sc

    where sub_name='Oracle'

     

    S_ID

    S_NAME

    SUB_NAME

    SCORE

    名次

    4             

    杨过                        

    Oracle            

    77.00                    

    1                     

    2

    李四   

    Oracle

    77.00

    1

    3

    张三丰 

    Oracle

    0.00

    3

     

     

    对比:rank()与dense_rank():非连续排名与连续排名(都是简单排名)

     

    select sc.s_id,sc.s_name,sub_name,sc.score,

     dense_rank() over (order by score desc名次

    from t_score sc

    where sub_name='Oracle'

    S_ID

    S_NAME

    SUB_NAME

    SCORE

    名次

    4                      

    杨过                 

    Oracle

    77.00                  

    1                      

    2

    李四   

    Oracle

    77.00

    1

    3

    张三丰 

    Oracle

    0.00

    2

     

    ·查询各学生各科排名(分区排名)

    select sc.s_id,sc.s_name,sub_name,sc.score,

     rank() over

     (partition by sub_name order by score desc名次

    from t_score sc

    S_ID

    S_NAME

    SUB_NAME

    SCORE

    名次

    4                  

    杨过                      

    JAVA                       

    90.00                  

    1                       

    4

    杨过   

    Oracle

    77.00

    1

    2

    李四   

    Oracle

    77.00

    1

    3

    张三丰 

    Oracle

    0.00

    3

    5

    mike   

    c++

    80.00

    1

    3

    张三丰 

    数学

     

    1

    2

    李四   

    数学

    80.00

    2

    1

    张三   

    数学

    0.00

    3

    3

    张三丰 

    体育

    120.00

    1

    1

    张三   

    语文

    80.00

    1

    2

    李四   

    语文

    50.00

    2

    3

    张三丰 

    语文

    10.00

    3

     

    ·查询各科前2名(分区排名)

    ·类似:新闻表,求栏目点击率在前3位的新闻。

    商品表,求各类别销售额在前10位的商品。

    select * from (

    select sc.s_id,sc.s_name,sub_name,sc.score,

    dense_rank() over

    (partition by sub_name order by score desc) 名次

    from t_score sc

    ) x

    where x.名次<=2

    S_ID

    S_NAME

    SUB_NAME

                    SCORE

       名次

    4                     

    杨过                        

    JAVA                    

                       90.00 

           1                            

    4

    杨过   

    Oracle

    77.00

           1

    2

    李四   

    Oracle

    77.00

           1

    3

    张三丰 

    Oracle

    0.00

           2

    5

    mike   

    c++

    80.00

           1

    3

    张三丰 

    数学

     

           1

    2

    李四   

    数学

    80.00

           2

    3

    张三丰 

    体育

    120.00

           1

    1

    张三   

    语文

    80.00 

           1 

    2

    李四   

    语文

    50.00

           2

     

     

    ·查询各同学总分

    select s_id,s_name,sum(score) sum_score from t_score

    group by s_id,s_name

    S_ID

    S_NAME

    SUM_SCORE

    1                       

    张三                         

    80.00                                 

    2

    李四   

    207.00

    3

    张三丰 

    130.00

    4

    杨过   

    167.00

    5

    mike   

    80.00

     

    ·根据总分查询各同学名次

    select x.*,

    rank() over (order by sum_score desc名次

    from (

    select s_id,s_name,sum(score) sum_score from t_score

    group by s_id,s_name ) x

    S_ID

    S_NAME

    SUM_SCORE

    名次

    2                   

    李四                       

    207.00                       

    1

    4

    杨过   

    167.00

    2

    3

    张三丰 

    130.00

    3

    1

    张三   

    80.00

    4

    5

    mike   

    80.00

    4

     

     

    语法:

    rank() over (order by 排序字段 顺序)

    rank() over (partition by 分组字段 order by 排序字段 顺序)

     

    1.顺序:asc|desc  名次与业务相关:

      示例:找求优秀学员:成绩:降序  迟到次数:升序

    2.分区字段:根据什么字段进行分区。

     

    问题:分区与分组有什么区别?

    ·分区只是将原始数据进行名次排列(记录数不变),

    ·分组是对原始数据进行聚合统计(记录数变少,每组返回一条),注意:聚合。

     

    脚本:

    create table t_score

    (

      autoid   number  primary key,

      s_id     number(3),

      s_name   char(8) not null,

      sub_name varchar2(20),

      score    number(10,2)

    );

    insert into t_score (autoid, s_id, s_name, sub_name, score)

    values (8, 1, '张三    ', '语文', 80);

    insert into t_score (autoid, s_id, s_name, sub_name, score)

    values (9, 2, '李四    ', '数学', 80);

    insert into t_score (autoid, s_id, s_name, sub_name, score)

    values (10, 1, '张三    ', '数学', 0);

    insert into t_score (autoid, s_id, s_name, sub_name, score)

    values (11, 2, '李四    ', '语文', 50);

    insert into t_score (autoid, s_id, s_name, sub_name, score)

    values (12, 3, '张三丰  ', '语文', 10);

    insert into t_score (autoid, s_id, s_name, sub_name, score)

    values (13, 3, '张三丰  ', '数学', null);

    insert into t_score (autoid, s_id, s_name, sub_name, score)

    values (14, 3, '张三丰  ', '体育', 120);

    insert into t_score (autoid, s_id, s_name, sub_name, score)

    values (15, 4, '杨过    ', 'java', 90);

    insert into t_score (autoid, s_id, s_name, sub_name, score)

    values (16, 5, 'mike    ', 'c++', 80);

    insert into t_score (autoid, s_id, s_name, sub_name, score)

    values (3, 3, '张三丰  ', 'oracle', 0);

    insert into t_score (autoid, s_id, s_name, sub_name, score)

    values (4, 4, '杨过    ', 'oracle', 77);

    insert into t_score (autoid, s_id, s_name, sub_name, score)

    values (17, 2, '李四    ', 'oracle', 77);

    commit;

     


    展开全文
  • oracle 排名函数

    千次阅读 2008-12-01 17:50:00
    rank()对表中的数据进行分级排序. 譬如有张学生成绩统计单的表STUDENT_RESULT name number kemu fenshu li 0113101 高数 90 zhang 0113098 高数 80 wang 0113077 高数 70 li 0113101 物理 80 zhang 0113098 物理 90 ...
  • 定义:ROW_NUMBER()函数作用就是将select查询到的数据进行排序,每一条数据加一个序号,他不能用做于学生成绩的排名,一般多用于分页查询,  比如查询前10个 查询10-100个学生。 实例: 1.1对学生成绩排序 &...
  • oracle排名函数rank()

    2011-06-17 15:29:00
    记录一个常见的例子供学习参考连续排名dense_rank();不连续排名rank()分组partition;不分组原始表:t_score姓名 学科 成绩张三 english 80李四 english 70王五 english 90赵六 english 
  • oracle排名函数使用

    2015-04-10 17:53:41
    Rank()使用说明: ... 返回结果集分区内指定字段的值的排名,指定字段的值的排名是相关行之前的排名加一。 b. 语法:  RANK() OVER([]) c. 参数说明:  partition_by_clause 将from子句生成
  • 一共有3种 rank()并列 不连续 dense_rank()并列 连续 row_number()不并列 连续
  • 一、几个排名函数的语法和用法:  rank ( ) over ([partition by col] order by col ) dense_rank ( ) over ([partition by col] order by col ) rownumber ( ) over ( [partition by col] order by col ) ...
  • Oracle创建函数

    千次阅读 2019-07-03 09:41:27
    Oracle创建函数语法 PL/SQL函数主要有下面几部分组成: 输入部分:PL/SQL函数可以有输入参数,在调用函数时,必须给输入参数赋值。 逻辑计算部分:逻辑计算部分是由PL/SQL块组成业务逻辑计算部分。这部分主要是...
  • 要求展示年度、月份、绩效工资、排名,横向展示12个月绩效工资及排名 create table TB_PERFORMANCE ( id NUMBER, pyear NUMBER, pmonth NUMBER, salary NUMBER(15,2), emplid NUMBER ) ...
  • oracle分析函数-计算移动平均、累计、分布

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 9,825
精华内容 3,930
关键字:

oracle排名函数