`
tianyihuyidao9
  • 浏览: 165937 次
  • 性别: Icon_minigender_1
  • 来自: 济南
文章分类
社区版块
存档分类
最新评论

使用分析函数来为记录排名的问题

 
阅读更多
[size=large]现在有三个表student,score,course
对应的建表语句如下：
CREATE TABLE student(
student_id NUMBER PRIMARY KEY,
student_name,VARCHAR2(30) NOT NULL)

CREATE TABLE score(
score_id NUMBER PRIMARY KEY,
student_id NUMBER,
course_id NUMBER,
score NUMBER)

CREATE TABLE course(
course_id NUMBER PRIMARY KEY,
course_name VARCHAR2(30))

要求用基本SQL实现如下的两条查询要求：
(1) 求出每门课程成绩排名前五名的同学的姓名，分数和课程名
(2)求出每门课程成绩排名第三的同学的姓名，分数和课程名

Oracle的解法：
表及数据：

Sql代码
<SPAN style="FONT-SIZE: large">create table STUDENT  
(  
  STUDENT_ID   NUMBER not null,  
  STUDENT_NAME VARCHAR2(30) not null 
)  
;  
alter table STUDENT  
  add primary key (STUDENT_ID);  
 
prompt Loading STUDENT...  
insert into STUDENT (STUDENT_ID, STUDENT_NAME)  
values (1, '张三');  
insert into STUDENT (STUDENT_ID, STUDENT_NAME)  
values (2, '李四');  
insert into STUDENT (STUDENT_ID, STUDENT_NAME)  
values (3, '王五');  
insert into STUDENT (STUDENT_ID, STUDENT_NAME)  
values (4, '马六');  
insert into STUDENT (STUDENT_ID, STUDENT_NAME)  
values (5, '孙七');  
insert into STUDENT (STUDENT_ID, STUDENT_NAME)  
values (6, '王八');  
commit;</SPAN> 

create table STUDENT
(
  STUDENT_ID   NUMBER not null,
  STUDENT_NAME VARCHAR2(30) not null
)
;
alter table STUDENT
  add primary key (STUDENT_ID);

prompt Loading STUDENT...
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (1, '张三');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (2, '李四');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (3, '王五');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (4, '马六');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (5, '孙七');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (6, '王八');
commit;  Sql代码
<SPAN style="FONT-SIZE: large">create table COURSE  
(  
  COURSE_ID   NUMBER not null,  
  COURSE_NAME VARCHAR2(30)  
)  
;  
alter table COURSE  
  add primary key (COURSE_ID);  
 
prompt Loading COURSE...  
insert into COURSE (COURSE_ID, COURSE_NAME)  
values (1, '语文');  
insert into COURSE (COURSE_ID, COURSE_NAME)  
values (2, '数学');  
insert into COURSE (COURSE_ID, COURSE_NAME)  
values (3, '英语');  
commit;</SPAN> 

create table COURSE
(
  COURSE_ID   NUMBER not null,
  COURSE_NAME VARCHAR2(30)
)
;
alter table COURSE
  add primary key (COURSE_ID);

prompt Loading COURSE...
insert into COURSE (COURSE_ID, COURSE_NAME)
values (1, '语文');
insert into COURSE (COURSE_ID, COURSE_NAME)
values (2, '数学');
insert into COURSE (COURSE_ID, COURSE_NAME)
values (3, '英语');
commit;  Sql代码
<SPAN style="FONT-SIZE: large">create table SCORE  
(  
  SCORE_ID   NUMBER not null,  
  STUDENT_ID NUMBER,  
  COURSE_ID  NUMBER,  
  SCORE      NUMBER  
)  
;  
alter table SCORE  
  add primary key (SCORE_ID);  
 
prompt Loading SCORE...  
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
values (1, 1, 1, 99);  
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
values (2, 1, 2, 98);  
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
values (3, 1, 3, 97);  
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
values (4, 2, 1, 99);  
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
values (5, 2, 2, 97);  
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
values (6, 2, 3, 98);  
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
values (7, 3, 1, 96);  
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
values (8, 3, 2, 95);  
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
values (9, 3, 3, 94);  
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
values (10, 4, 1, 93);  
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
values (11, 4, 2, 92);  
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
values (12, 4, 3, 91);  
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
values (13, 5, 1, 90);  
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
values (14, 5, 2, 89);  
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
values (15, 5, 3, 88);  
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
values (16, 6, 1, 87);  
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
values (17, 6, 2, 86);  
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)  
values (18, 6, 3, 85);  
commit;</SPAN> 

create table SCORE
(
  SCORE_ID   NUMBER not null,
  STUDENT_ID NUMBER,
  COURSE_ID  NUMBER,
  SCORE      NUMBER
)
;
alter table SCORE
  add primary key (SCORE_ID);

prompt Loading SCORE...
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (1, 1, 1, 99);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (2, 1, 2, 98);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (3, 1, 3, 97);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (4, 2, 1, 99);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (5, 2, 2, 97);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (6, 2, 3, 98);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (7, 3, 1, 96);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (8, 3, 2, 95);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (9, 3, 3, 94);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (10, 4, 1, 93);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (11, 4, 2, 92);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (12, 4, 3, 91);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (13, 5, 1, 90);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (14, 5, 2, 89);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (15, 5, 3, 88);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (16, 6, 1, 87);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (17, 6, 2, 86);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (18, 6, 3, 85);
commit;

(1) 求出每门课程成绩排名前五名的同学的姓名，分数和课程名：
根据不同的排名方式有三种不同的sql写法：
1.1成绩相同的人排名相同，且排名是连续的。
Sql如下：

Sql代码
<SPAN style="FONT-SIZE: large">select *  
  from (select s.STUDENT_NAME,  
               sc.SCORE,  
               c.COURSE_NAME,  
               dense_rank() over(partition by c.COURSE_ID order by sc.SCORE desc) drank  
          from student s, course c, score sc  
         where s.STUDENT_ID = sc.STUDENT_ID  
           and c.COURSE_ID = sc.COURSE_ID) t  
where t.drank < 6;</SPAN> 

select *
  from (select s.STUDENT_NAME,
               sc.SCORE,
               c.COURSE_NAME,
               dense_rank() over(partition by c.COURSE_ID order by sc.SCORE desc) drank
          from student s, course c, score sc
         where s.STUDENT_ID = sc.STUDENT_ID
           and c.COURSE_ID = sc.COURSE_ID) t
where t.drank < 6; 结果如下：

STUDENT_NAME SCORE COURSE_NAME DRANK
张三 99 语文 1
李四 99 语文 1
王五 96 语文 2
马六 93 语文 3
孙七 90 语文 4
王八 87 语文 5
张三 98 数学 1
李四 97 数学 2
王五 95 数学 3
马六 92 数学 4
孙七 89 数学 5
李四 98 英语 1
张三 97 英语 2
王五 94 英语 3
马六 91 英语 4

孙七 88 英语 5




1.2成绩相同的人排名相同，且排名不是连续的。
Sql如下：

Sql代码
<SPAN style="FONT-SIZE: large; COLOR: #000000">select *  
  from (select s.STUDENT_NAME,  
               sc.SCORE,  
               c.COURSE_NAME,  
               rank() over(partition by c.COURSE_ID order by sc.SCORE desc) ranking  
          from student s, course c, score sc  
         where s.STUDENT_ID = sc.STUDENT_ID  
           and c.COURSE_ID = sc.COURSE_ID) t  
where t.ranking < 6;</SPAN> 

select *
  from (select s.STUDENT_NAME,
               sc.SCORE,
               c.COURSE_NAME,
               rank() over(partition by c.COURSE_ID order by sc.SCORE desc) ranking
          from student s, course c, score sc
         where s.STUDENT_ID = sc.STUDENT_ID
           and c.COURSE_ID = sc.COURSE_ID) t
where t.ranking < 6; 结果如下：

STUDENT_NAME SCORE COURSE_NAME RANKING
张三 99 语文 1
李四 99 语文 1
王五 96 语文 3
马六 93 语文 4
孙七 90 语文 5
张三 98 数学 1
李四 97 数学 2
王五 95 数学 3
马六 92 数学 4
孙七 89 数学 5
李四 98 英语 1
张三 97 英语 2
王五 94 英语 3
马六 91 英语 4
孙七 88 英语 5

1.2成绩相同的人根据学号排序，排名是连续的。
Sql如下：

Sql代码
<SPAN style="FONT-SIZE: large">select *  
  from (select s.STUDENT_NAME,  
               sc.SCORE,  
               c.COURSE_NAME,  
               row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn  
          from student s, course c, score sc  
         where s.STUDENT_ID = sc.STUDENT_ID  
           and c.COURSE_ID = sc.COURSE_ID) t  
where t.rn < 6;</SPAN> 

select *
  from (select s.STUDENT_NAME,
               sc.SCORE,
               c.COURSE_NAME,
               row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn
          from student s, course c, score sc
         where s.STUDENT_ID = sc.STUDENT_ID
           and c.COURSE_ID = sc.COURSE_ID) t
where t.rn < 6; 结果如下：

STUDENT_NAME SCORE COURSE_NAME RN
张三 99 语文 1
李四 99 语文 2
王五 96 语文 3
马六 93 语文 4
孙七 90 语文 5
张三 98 数学 1
李四 97 数学 2
王五 95 数学 3
马六 92 数学 4
孙七 89 数学 5
李四 98 英语 1
张三 97 英语 2
王五 94 英语 3
马六 91 英语 4
孙七 88 英语 5



(2)求出每门课程成绩排名第三的同学的姓名，分数和课程名：
Sql如下：

Sql代码
<SPAN style="FONT-SIZE: large">select *  
  from (select s.STUDENT_NAME,  
               sc.SCORE,  
               c.COURSE_NAME,  
               row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn  
          from student s, course c, score sc  
         where s.STUDENT_ID = sc.STUDENT_ID  
           and c.COURSE_ID = sc.COURSE_ID) t  
where t.rn = 3;</SPAN> 

select *
  from (select s.STUDENT_NAME,
               sc.SCORE,
               c.COURSE_NAME,
               row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn
          from student s, course c, score sc
         where s.STUDENT_ID = sc.STUDENT_ID
           and c.COURSE_ID = sc.COURSE_ID) t
where t.rn = 3; 结果如下：

STUDENT_NAME SCORE COURSE_NAME RN
王五 96 语文 3
王五 95 数学 3
王五 94 英语 3

[/size]


Rank，Dense_rank，Row_number函数为每条记录产生一个从1开始至N的自然数，N的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。

①ROW_NUMBER：

Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。

②DENSE_RANK：
Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。

③RANK：
Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。


http://www.cnblogs.com/wuyisky/archive/2010/02/24/oracle_rank.html
http://www.iteye.com/job/topic/1112015
分享到：
| Oracle基础知识
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    ORACLE分析函数.ppt

    这段代码使用分析函数将查询结果排序，并返回前一行记录。 6. 小结 ORACLE分析函数是一种功能强大且灵活的分析工具，能够对数据进行复杂的分析和处理。通过使用分析函数，开发者可以更加方便地实现业务逻辑，提高...

    oracle 9i 分析函数参考手册.rar

    在Oracle数据库系统中，分析函数为数据分析师和数据库管理员提供了深入洞察数据的能力，尤其在处理排序数据集时，它们能提供非常灵活的功能。 分析函数的基本概念： 1. 分析函数与聚合函数（如SUM, COUNT, AVG等）...

    Ooracle 分析函数的使用实例

    本文将通过具体示例，深入探讨Oracle分析函数的几个关键应用，包括：汇总函数ROLLUP和CUBE、排名函数RANK和DENSE_RANK、LAG和LEAD函数、SUM和AVG的移动计算、RATIO_TO_REPORT报表处理函数以及FIRST和LAST取基数函数...

    Oracle_详解分析函数

    本文将详细介绍Oracle分析函数，并通过具体实例来帮助读者更好地理解和应用这些功能。 #### 二、分析函数分类与应用 分析函数主要分为五大类： 1. **等级函数**：如`RANK`, `DENSE_RANK`, `ROW_NUMBER`等，用于对...

    Oracle 分析函数使用介绍

    在本文中，我们将深入探讨Oracle分析函数的使用方法，以及它们如何帮助我们进行数据统计和分析。 1. **窗口函数概念** 分析函数也被称为窗口函数，因为它们在数据集的一个“窗口”或分区上操作，这个窗口可以包含...

    oracle分析函数及开窗函数

    开窗函数是分析函数的一种特殊形式，它允许用户指定一个“窗口”或数据段来进行计算。开窗函数的`OVER`子句可以包含以下几种类型： 1. **PARTITION BY**：将数据集分割成多个分区，在每个分区上分别进行计算。 2. *...

    oracle分析函数.doc

    假设我们有一个销售表，包含产品ID、销售日期和销售额，我们可以使用`ROW_NUMBER()`来为每条记录分配一个顺序编号，或者使用`SUM()`函数计算每个产品的累计销售额。 ```sql SELECT product_id, sale_date, sale_...

    oracle分析函数的使用

    1. 传统的统计函数，如`COUNT()`，通常与`GROUP BY`一起使用来计算每个类别的数量。例如，我们可以计算每个班级的学生数量： ```sql SELECT t.class, COUNT(*) FROM t_student_score t GROUP BY t.class; ``` 如果...

    分析函数,数据库开发

    ### Oracle分析函数详解 #### 一、概述 在数据库开发过程中，为了提高查询效率和简化复杂操作，Oracle引入了一系列分析函数。这些函数可以帮助开发者轻松地完成数据聚合、排序、统计等任务，相比于传统的方法（如...

    ORACLE报表分析利剑——分析函数

    为了更好地理解和应用分析函数，通常会创建一个演示表，填充必要的数据，以便通过实际案例来解释和展示函数的工作原理。 ##### 3.2 `FIRST_VALUE()`与`LAST_VALUE()`：捕捉极值 这两个函数分别用于获取分组内某列...

    Oracle分析函数使用总结

    在Oracle数据库中，分析函数提供了一种强大的方式来处理分组数据，其中评级函数尤其在评估数据排名时极为有用。以下是对几种常用的评级函数的详细介绍： 1. **RANK()**：此函数返回数据项在分组中的排名。其特点是...

    ORACLE分析函数.pptx

    新的分析函数SQL只需要一次表访问，通过`rn=1`来筛选出排名最高的记录。这种方法减少了逻辑读和COST，提升了查询效率。 然而，即使使用了分析函数，查询仍然需要进行排序操作，这可能会带来额外的开销。为了解决这...

    ORACLE分析函数1.ppt

    在这个例子中，我们用它来计算每个owner的created日期的排名，然后只选择排名为1的记录，即最近一天的数据。分析函数的版本逻辑读降低到了423，COST降至693，这意味着查询效率提高了，因为它减少了对表或索引的访问...

    SQL分析函数进阶

    分析函数的简述提供了基础概念和使用示例，对于初学者来说是很好的学习资源。通过阅读和实践，可以逐步掌握这些功能强大的工具。 在OLAP系统中，分析函数扮演着至关重要的角色，因为它们能高效地处理大量数据，...

    Oracle10gR2分析函数

    基于提供的文件信息，我们可以深入探讨如何使用Oracle 10g R2中的分析函数来解决描述中的业务需求。这里的关键在于理解分析函数的工作原理，并结合实际案例来设计解决方案。 ### Oracle 10g R2 分析函数简介 ...

    Oracle分析函数

    Oracle分析函数为数据处理提供了强大的工具箱，使得开发者能够轻松地执行复杂的统计分析和数据处理任务。通过熟练掌握这些函数，可以在数据分析、报告生成等方面大大提高效率。无论是简单的聚合操作还是复杂的分布...

    Oracle分析函数使用总结[定义].pdf

    Oracle分析函数是数据库查询中非常强大的工具，它们用于在数据集上执行聚合操作，并返回每个行的上下文信息。...通过深入学习和实践，开发者可以更好地利用Oracle分析函数来优化和增强他们的数据库查询和报表生成能力。

    【转】Oracle分析函数简述

    5. SUM() 分析函数：对一组行进行求和，同样可以使用OVER()子句来定义求和的范围。 6. COUNT() 分析函数：计数特定列中的非空值或所有行。 7. MAX() 和 MIN() 分析函数：分别找出一组行中的最大值和最小值。 8. ...

Magicbox
Global site tag (gtag.js) - Google Analytics