`
tianyihuyidao9
  • 浏览: 159082 次
  • 性别: 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_详解分析函数

    详解Oracle分析函数,主用于OLAP,以实例讲解分析函数. 如: 排序用Rank, Dense_rank, row_number 1.带空值的排列 2.Top/Bottom N查询 3.First/Last排名查询 4.按层次查询 1.窗口函数简介 2.窗口函数示例-全统计 3....

    oracle常用分析函数与聚合函数的用法

    今天是2019年第一天，在此祝大家新年快乐，梦想还在路上，让我们继续加油！ 应之前的计划，今天完成这篇记录，也借此记录自己...rank() 是排名的函数，该函数组内排序后会进行跳号，分数相同的作为并列。 dense_rank()

    Oracle开发之分析函数(Rank, Dense_rank, row_number)

    一、使用rownum为记录排名： 在前面一篇《Oracle开发之分析函数简介Over》，我们认识了分析函数的基本应用，现在我们再来考虑下面几个问题： ①对所有客户按订单总额进行排名 ②按区域和客户订单总额进行排名 ③找出...

    论文研究 - 根据地震，台风和环境振动的记录数据分析51层塔的振动

    这项研究使用结构内记录来调查一栋51层钢高层建筑在地震，台风和环境振动的作用下的振动特性。 这为我们提供了一个比较建筑物行为的机会，特别是在不同类型的激励下它们的模态特性。 首先，我们使用两阶段回归程序来...

    Excel函数活用范例大辞典（全新版）.何先军.2015-2(带书签高清文字版).pdf

    本书侧重于函数的实战应用，共分12章，前10章分别介绍了数学函数、统计函数、日期和时间函数、文本函数、逻辑函数、查找和引用函数、财务函数、信息函数以及数据库和三角函数在实战中的应用；第11 章介绍了函数与...

    Excel数据分析与图表应用案例精粹_光盘

    　9.3.3 使用SUMPRODUCT函数精确分析 158 　9.3.4 本期客户交易评估 161 　9.4 利用数据透视表分析销售数据 164 　9.4.1 分析客户采购情况 164 　9.4.2 分析应收账款 166 　第10章 产品库存数据处理与分析 169 　...

    《Excel应用大全》示例文件 光盘文件

    • 使用记录单为数据列表命名动态名称 • 数据列表按行排序 • 含有公式的数据排序 • 简单排序的例子 • 自定义排序 • “关系或”条件的高级筛选 • “关系与”条件的高级筛选 • 利用高级筛选选择不重复的记录 •...

    中小学简易成绩分析系统

    简易成绩分析系统使用说明 四川省泸州市纳溪区大渡中学教务室制作使用 ※※※※一、特色 1、 EXCEL平台 本作品采用EXCEL 公式+VBA制作，既可根据你平时使用EXCEL的习惯和方式操作充分发挥EXCEL的功能，又能利用本...

    Excel 2007数据透视表完全剖析 1/7

    然后逐步介绍创建数据透视表、自定义透视表、查看视图数据、在透视表内进行计算、使用数据透视图等可视化工具、分析数据源、共享数据表、使用和分析OLAP数据、在透视表中使用宏和VBA等内容，并集中解答了一些常见的...

    Excel 2007数据透视表完全剖析 5/7

    然后逐步介绍创建数据透视表、自定义透视表、查看视图数据、在透视表内进行计算、使用数据透视图等可视化工具、分析数据源、共享数据表、使用和分析OLAP数据、在透视表中使用宏和VBA等内容，并集中解答了一些常见的...

    Excel 2007数据透视表完全剖析 3/7

    然后逐步介绍创建数据透视表、自定义透视表、查看视图数据、在透视表内进行计算、使用数据透视图等可视化工具、分析数据源、共享数据表、使用和分析OLAP数据、在透视表中使用宏和VBA等内容，并集中解答了一些常见的...

    Excel 2007数据透视表完全剖析 4/7

    然后逐步介绍创建数据透视表、自定义透视表、查看视图数据、在透视表内进行计算、使用数据透视图等可视化工具、分析数据源、共享数据表、使用和分析OLAP数据、在透视表中使用宏和VBA等内容，并集中解答了一些常见的...

    中小学学生成绩管理系统EXCEL V1.0版

    简易成绩分析系统使用说明 四川省泸州市纳溪区大渡中学 石明富制作 电话：13551664747 email:ddzx2122@sina.com ※※※※一、特色 1、EXCEL平台 本作品采用EXCEL 公式+VBA制作，既可根据你平时使用EXCEL的习惯和...

    中小学简易成绩管理系统(excel)

    简易成绩分析系统使用说明 四川省泸州市纳溪区大渡中学教务室制作使用 ※※※※一、特色 EXCEL平台 本作品采用EXCEL 公式+VBA制作，既可根据你平时使用EXCEL的习惯和方式操作充分发挥EXCEL的功能，又能利用本作品...

    C语言课程设计运动会成绩管理系统.doc

    每一个学生记录都包含学号、姓名、系别，以及赛跑比赛、跳远比赛、跳高比赛三 门成绩，在程序当中，将学生记录类型定义为结构体类型，添加以及追加的学生信息直 接写入定义的结构体中，其它函数每次对学生记录的...

    经典全面的SQL语句大全

    　15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) select a,b,c from tablename ta where a=(select max(a) ...

    Microsoft SQL Server 2008技术内幕：T-SQL查询(第二卷)

    主要内容包括SQL的基础理论、查询优化、查询算法及复杂度，以及在使用子查询、表表达式、排名函数、数据聚合和透视转换、TOP和APPLY、数据修改、分区表、特殊数据结构等实际应用时会遇到的各种高级查询问题和解决...

    excel成绩管理系统

    简易成绩分析系统使用说明 四川省泸州市纳溪区大渡中学 石明富制作 电话：13551664747 email:ddzx2122@sina.com ※※※※一、特色 1、EXCEL平台 本作品采用EXCEL 公式+VBA制作，既可根据你平时使用EXCEL的习惯和...

    C语言课程设计-学生成绩管理系统.zip

    可以把这些信息设为结构体成员，用结构体数组来存放45个学生的信息，并且为方便输入信息可以定义一个全局变量N。 2、将系统功能分为五大模块：读取，管理，统计，查询和保存。另外在程序中需要增加读取文件信息的...

    SQLServer2008技术内幕T-SQL查询包含源代码及附录A

    主要内容包括SQL的基础理论、查询优化、查询算法及复杂度，以及在使用子查询、表表达式、排名函数、数据聚合和透视转换、TOP和APPLY、数据修改、分区表、特殊数据结构等实际应用时会遇到的各种高级查询问题和解决...

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