row_number() 顺序号码, 也就是 行号, 比如 1,2,3,4,5 这样的顺序。 over 语法需要,必须的。 partition by name, kecheng,score 是按照 name, kecheng,score 分区。 也就是 如果有 不同的 name, kecheng,score , 这个 序号又重新从1开始计算。 order by rowid 是 排序方式, 也就是 最小的 rowid , row_number() 是1,然后随着 rowid 的增加, row_number() 不断递增。
建立表,插入数据
create table PERSON
(
firstname VARCHAR2(10),
age INTEGER,
gender CHAR(1),
grade NUMBER
)
(
firstname VARCHAR2(10),
age INTEGER,
gender CHAR(1),
grade NUMBER
)
insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('Ted', 23, 'M', 85);
values ('Ted', 23, 'M', 85);
insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('John', 40, 'M', 86);
values ('John', 40, 'M', 86);
insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('George', 6, 'M', 56);
values ('George', 6, 'M', 56);
insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('Mary', 11, 'F', 80);
values ('Mary', 11, 'F', 80);
insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('Sam', 17, 'M', 72);
values ('Sam', 17, 'M', 72);
insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('Doris', 6, 'F', 53);
values ('Doris', 6, 'F', 53);
insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('Frank', 38, 'M', 56);
values ('Frank', 38, 'M', 56);
insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('Larry', 5, 'M', 85);
values ('Larry', 5, 'M', 85);
insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('Sue', 29, 'F', 68);
values ('Sue', 29, 'F', 68);
insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('Sherry', 11, 'F', 60);
values ('Sherry', 11, 'F', 60);
insert into PERSON (FIRSTNAME, AGE, GENDER, GRADE)
values ('Marty', 23, 'F', 85);
values ('Marty', 23, 'F', 85);
row_number() over(),rank() over(),dense_rank() over的区别:
select row_number() over(order by age) as num1,firstname,age,gender from person;--按年龄升序排名,相同年龄位次递加
select rank() over(order by age) as num1,firstname,age,gender from person;--按年龄升序排名,找出并列,跳跃排名
select dense_rank() over(order by age) as num1,firstname,age,gender from person;--按年龄升序排名,找出并列,依次排名
select rank() over(order by age) as num1,firstname,age,gender from person;--按年龄升序排名,找出并列,跳跃排名
select dense_rank() over(order by age) as num1,firstname,age,gender from person;--按年龄升序排名,找出并列,依次排名
以下为执行结果
select row_number() over(order by age) as num1,firstname,age,gender from person;--按年龄升序排名,相同年龄位次递加
NUM1 FIRSTNAME AGE GENDER
---------- ---------- --------------------------------------- ------
1 Larry 5 M
2 Doris 6 F
3 George 6 M
4 Mary 11 F
5 Sherry 11 F
6 Sam 17 M
7 Marty 23 F
8 Ted 23 M
9 Sue 29 F
10 Frank 38 M
11 John 40 M
NUM1 FIRSTNAME AGE GENDER
---------- ---------- --------------------------------------- ------
1 Larry 5 M
2 Doris 6 F
3 George 6 M
4 Mary 11 F
5 Sherry 11 F
6 Sam 17 M
7 Marty 23 F
8 Ted 23 M
9 Sue 29 F
10 Frank 38 M
11 John 40 M
select rank() over(order by age) as num1,firstname,age,gender from person;--按年龄升序排名,找出并列,跳跃排名
NUM1 FIRSTNAME AGE GENDER
---------- ---------- --------------------------------------- ------
1 Larry 5 M
2 Doris 6 F
2 George 6 M
4 Mary 11 F
4 Sherry 11 F
6 Sam 17 M
7 Marty 23 F
7 Ted 23 M
9 Sue 29 F
10 Frank 38 M
11 John 40 M
NUM1 FIRSTNAME AGE GENDER
---------- ---------- --------------------------------------- ------
1 Larry 5 M
2 Doris 6 F
2 George 6 M
4 Mary 11 F
4 Sherry 11 F
6 Sam 17 M
7 Marty 23 F
7 Ted 23 M
9 Sue 29 F
10 Frank 38 M
11 John 40 M
select dense_rank() over(order by age) as num1,firstname,age,gender from person;--按年龄升序排名,找出并列,依次排名
/
NUM1 FIRSTNAME AGE GENDER
---------- ---------- --------------------------------------- ------
1 Larry 5 M
2 Doris 6 F
2 George 6 M
3 Mary 11 F
3 Sherry 11 F
4 Sam 17 M
5 Marty 23 F
5 Ted 23 M
6 Sue 29 F
7 Frank 38 M
8 John 40 M
/
NUM1 FIRSTNAME AGE GENDER
---------- ---------- --------------------------------------- ------
1 Larry 5 M
2 Doris 6 F
2 George 6 M
3 Mary 11 F
3 Sherry 11 F
4 Sam 17 M
5 Marty 23 F
5 Ted 23 M
6 Sue 29 F
7 Frank 38 M
8 John 40 M
select row_number() over(partition by gender order by age) as part ,firstname,age,gender from person;--先按性别分组,再按年龄排序
PART FIRSTNAME AGE GENDER
---------- ---------- --------------------------------------- ------
1 Doris 6 F
2 Sherry 11 F
3 Mary 11 F
4 Marty 23 F
5 Sue 29 F
1 Larry 5 M
2 George 6 M
3 Sam 17 M
4 Ted 23 M
5 Frank 38 M
6 John 40 M
PART FIRSTNAME AGE GENDER
---------- ---------- --------------------------------------- ------
1 Doris 6 F
2 Sherry 11 F
3 Mary 11 F
4 Marty 23 F
5 Sue 29 F
1 Larry 5 M
2 George 6 M
3 Sam 17 M
4 Ted 23 M
5 Frank 38 M
6 John 40 M
关于sum()和over()的联合使用:
select sum(grade) over(partition by age order by firstname) as sum1 ,firstname,age,gender,grade from person;--先按年龄分组,再按名字排序后,按年龄“连续”求分数总和
select sum(grade) over(partition by age) as sum1 ,firstname,age,gender from person;-- 按年龄分组后,求分数总和
select sum(grade) over(order by age,firstname) as sum1 ,firstname,age,gender,grade from person;--先按年龄排序,再按名字排序,“连续”求分数总和
select sum(grade) over(partition by age) as sum1 ,firstname,age,gender from person;-- 按年龄分组后,求分数总和
select sum(grade) over(order by age,firstname) as sum1 ,firstname,age,gender,grade from person;--先按年龄排序,再按名字排序,“连续”求分数总和
select firstname,age,gender,sum(grade) over(partition by null) as sum1 from person;
=select firstname,age,gender,sum(grade) over() as sum1 from person;--不分组,求所有总和,等同于sum(grade)
=select firstname,age,gender,sum(grade) over() as sum1 from person;--不分组,求所有总和,等同于sum(grade)
以下为执行结果:
select sum(grade) over(partition by age order by firstname) as sum1 ,firstname,age,gender,grade from person;--先按年龄分组,再按名字排序后,按年龄“连续”求分数总和
SUM1 FIRSTNAME AGE GENDER GRADE
---------- ---------- --------------------------------------- ------ ----------
85 Larry 5 M 85
53 Doris 6 F 53
109 George 6 M 56
80 Mary 11 F 80
140 Sherry 11 F 60
72 Sam 17 M 72
85 Marty 23 F 85
170 Ted 23 M 85
68 Sue 29 F 68
56 Frank 38 M 56
86 John 40 M 86
SUM1 FIRSTNAME AGE GENDER GRADE
---------- ---------- --------------------------------------- ------ ----------
85 Larry 5 M 85
53 Doris 6 F 53
109 George 6 M 56
80 Mary 11 F 80
140 Sherry 11 F 60
72 Sam 17 M 72
85 Marty 23 F 85
170 Ted 23 M 85
68 Sue 29 F 68
56 Frank 38 M 56
86 John 40 M 86
select sum(grade) over(partition by age) as sum1 ,firstname,age,gender from person;-- 按年龄分组后,求分数总和
SUM1 FIRSTNAME AGE GENDER
---------- ---------- --------------------------------------- ------
85 Larry 5 M
109 Doris 6 F
109 George 6 M
140 Mary 11 F
140 Sherry 11 F
72 Sam 17 M
170 Marty 23 F
170 Ted 23 M
68 Sue 29 F
56 Frank 38 M
86 John 40 M
SUM1 FIRSTNAME AGE GENDER
---------- ---------- --------------------------------------- ------
85 Larry 5 M
109 Doris 6 F
109 George 6 M
140 Mary 11 F
140 Sherry 11 F
72 Sam 17 M
170 Marty 23 F
170 Ted 23 M
68 Sue 29 F
56 Frank 38 M
86 John 40 M
select sum(grade) over(order by age,firstname) as sum1 ,firstname,age,gender,grade from person;--先按年龄排序,再按名字排序,“连续”求分数总和
SUM1 FIRSTNAME AGE GENDER GRADE
---------- ---------- --------------------------------------- ------ ----------
85 Larry 5 M 85
138 Doris 6 F 53
194 George 6 M 56
274 Mary 11 F 80
334 Sherry 11 F 60
406 Sam 17 M 72
491 Marty 23 F 85
576 Ted 23 M 85
644 Sue 29 F 68
700 Frank 38 M 56
786 John 40 M 86
SUM1 FIRSTNAME AGE GENDER GRADE
---------- ---------- --------------------------------------- ------ ----------
85 Larry 5 M 85
138 Doris 6 F 53
194 George 6 M 56
274 Mary 11 F 80
334 Sherry 11 F 60
406 Sam 17 M 72
491 Marty 23 F 85
576 Ted 23 M 85
644 Sue 29 F 68
700 Frank 38 M 56
786 John 40 M 86
select firstname,age,gender,sum(grade) over(partition by null) as sum1 from person;
=select firstname,age,gender,sum(grade) over() as sum1 from person;--不分组,求所有总和,等同于sum(grade)
FIRSTNAME AGE GENDER SUM1
---------- --------------------------------------- ------ ----------
Ted 23 M 786
John 40 M 786
George 6 M 786
Mary 11 F 786
Sam 17 M 786
Marty 23 F 786
Frank 38 M 786
Larry 5 M 786
Sue 29 F 786
Sherry 11 F 786
Doris 6 F 786
FIRSTNAME AGE GENDER SUM1
---------- --------------------------------------- ------ ----------
Ted 23 M 786
John 40 M 786
George 6 M 786
Mary 11 F 786
Sam 17 M 786
Marty 23 F 786
Frank 38 M 786
Larry 5 M 786
Sue 29 F 786
Sherry 11 F 786
Doris 6 F 786