Oracle Rank() Over()

来源：互联网发布：api接口 java 编辑：程序博客网时间：2024/06/05 12:49

Rank()使用说明：

a. 函数简介：

返回结果集分区内指定字段的值的排名，指定字段的值的排名是相关行之前的排名加一。

b. 语法：

RANK() OVER([<partiton_by_clause>]<order by clause>)

c. 参数说明：

partition_by_clause 将from子句生成的结果集划分为应用到RANK函数的分区。

Order_by_clause确定将RANK值应用到分区中的行时所使用的顺序。

d. 以下是实例使用：

1. 创建测试表

Sql代码

--创建表
-- Create table
create table T_SCORE
(
AUTOID NUMBER not null,
S_ID NUMBER(3),
S_NAME CHAR(8) not null,
SUB_NAME VARCHAR2(20),
SCORE NUMBER(10,2)
);
-- Add comments to the table
comment on table T_SCORE
is '学生成绩表';
-- Add comments to the columns
comment on column T_SCORE.AUTOID
is '主键ID';
comment on column T_SCORE.S_ID
is '学生ID';
comment on column T_SCORE.S_NAME
is '学生姓名';
comment on column T_SCORE.SUB_NAME
is '科目';
comment on column T_SCORE.SCORE
is '成绩';

2. 创建测试记录

Sql代码

insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)
values (8, 1, '张三 ', '语文', 80.00);
insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)
values (9, 2, '李四 ', '数学', 80.00);
insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)
values (10, 1, '张三 ', '数学', 0.00);
insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)
values (11, 2, '李四 ', '语文', 50.00);
insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)
values (12, 3, '张三丰 ', '语文', 10.00);
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.00);
insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)
values (15, 4, '杨过 ', 'JAVA', 90.00);
insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)
values (16, 5, 'mike ', 'c++', 80.00);
insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)
values (3, 3, '张三丰 ', 'Oracle', 0.00);
insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)
values (4, 4, '杨过 ', 'Oracle', 77.00);
insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)
values (17, 2, '李四 ', 'Oracle', 77.00);

3. 分不同情况查询

3.1 查询所有的学生成绩

Sql代码

--1.查询所有的学生成绩
select t.s_id 学号, t.s_name 姓名, t.sub_name 科目, t.score 成绩
from t_score t;

查询结果：

学号姓名科目成绩1张三语文80.002李四数学80.001张三数学0.002李四语文50.003张三丰语文10.003张三丰数学 3张三丰体育120.004杨过 JAVA90.005mike c++80.003张三丰 Oracle0.004杨过 Oracle77.002李四 Oracle77.00

3.2 查询Oracle科目成绩名次-非连续rank

Sql代码

--2.查询Oracle科目成绩名次-非连续rank
select t.s_id 学号,
t.s_name 姓名,
t.sub_name 科目,
t.score 成绩,
rank() over(order by score desc nulls last) 名次
from t_score t
where t.sub_name = 'Oracle';

查询结果：

学号姓名科目成绩名次4杨过 Oracle77.0012李四 Oracle77.0013张三丰 Oracle0.003

3.3查询Oracle科目成绩名次-连续dense_rank

Sql代码

--3.查询Oracle科目成绩名次-连续dense_rank
select t.s_id 学号,
t.s_name 姓名,
t.sub_name 科目,
t.score 成绩,
dense_rank() over(order by score desc nulls last) 名次
from t_score t
where t.sub_name = 'Oracle';

查询结果：

学号姓名科目成绩名次4杨过 Oracle77.0012李四 Oracle77.0013张三丰 Oracle0.002

3.4 查询各学生各科排名

Sql代码

--4.查询各学生各科排名
select t.s_id 学号,
t.s_name 姓名,
t.sub_name 科目,
t.score 成绩,
dense_rank() over(partition by t.s_name order by score desc nulls last) 名次
from t_score t;

查询结果：

学号姓名科目成绩名次5mike c++80.0012李四数学80.0012李四 Oracle77.0022李四语文50.0034杨过 JAVA90.0014杨过 Oracle77.0021张三语文80.0011张三数学0.0023张三丰体育120.0013张三丰语文10.0023张三丰 Oracle0.0033张三丰数学 4

3.5 查询各科名次（分区）

Sql代码

--5.查询各科名次（分区）
select t.s_id 学号,
t.s_name 姓名,
t.sub_name 科目,
t.score 成绩,
dense_rank() over(partition by t.sub_name order by score desc nulls last) 名次
from t_score t;

查询结果：

学号姓名科目成绩名次4杨过 JAVA90.0014杨过 Oracle77.0012李四 Oracle77.0013张三丰 Oracle0.0025mike c++80.0012李四数学80.0011张三数学0.0023张三丰数学 33张三丰体育120.0011张三语文80.0012李四语文50.0023张三丰语文10.003

来源：http://zhaisx.iteye.com/blog/774165

阅读全文

0 0