-
Oracle 的开窗函数使用详解（一）
Oracle 的开窗函数使用详解（一）
一、开窗函数概述
开窗函数用于为行定义一个窗口（这里的窗口是指运算将要操作的行的集合），对一组值进行操作但不需要使用 GROUP BY 子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。
开窗函数与聚合函数计算方式一样，但它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值。
开窗函数的语法为：over(partition by 列名1 order by 列名2)，括号中的两个关键词 partition by 和 order by 可以只出现一个。over() 前面是一个函数，如果是聚合函数，那么 order by 不能一起使用。
开窗函数主要分为以下两类：
1、排名开窗函数
包括：ROW_NUMBER()、DENSE_RANK()、RANK() 等函数。排名开窗函数可以单独使用 ORDER BY 语句，也可以和 PARTITION BY 同时使用。
PARTITION BY 用于将结果集进行分组，开窗函数应用于每一组。ORDER BY 指定排名开窗函数的顺序，在排名开窗函数中必须使用 ORDER BY 语句。
ROW_NUMBER() 为每一组的行按顺序生成一个连续序号。
RANK() 也为每一组的行生成一个序号，与 ROW_NUMBER() 不同的是如果按照 ORDER BY 排序，如果有相同的值会生成相同的序号，并且接下来的序号是不连序的。例如：两个相同的行生成序号 2，那么接下来会生成序号 4。
DENSE_RANK() 和 RANK() 类似，不同的是如果有相同的序号，那么接下来的序号不会间断。也就是说如果两个相同的行生成序号2，那么接下来生成的序号还是3。
2、聚合开窗函数
很多聚合函数都可以用作窗口函数的运算，如 SUM()、AVG()、MAX()、MIN()、COUNT()。
开窗函数的语法格式如下：
-- 格式： 函数名(列名) OVER(选项) /* 说明： （1）OVER 关键字：表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用 OVER 关键字来区分这两种用法。 （2）如果 OVER 关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。 （3）PARTITION BY 子句：使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。与 GROUP BY 子句不同，PARTITION BY 子句创建的分区是独立于结果集的，创建的分区只是用于聚合计算，而且不同的开窗函数所创建的分区互不影响。 （4）ORDER BY 子句：ORDER BY 子句指定排序规则。使用 ORDER BY 子句可以对结果集按照指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算。 */ -- 常用开窗函数 row_number() over(partition by ... order by ...) rank() over(partition by ... order by ...) dense_rank() over(partition by ... order by ...) count() over(partition by ... order by ...) max() over(partition by ... order by ...) min() over(partition by ... order by ...) sum() over(partition by ... order by ...) avg() over(partition by ... order by ...) first_value() over(partition by ... order by ...) last_value() over(partition by ... order by ...) lag() over(partition by ... order by ...) lead() over(partition by ... order by ...）- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
二、排名开窗函数的使用举例
1、创建表，并添加数据
/* create table stu( s_id char(5) primary key, s_name varchar2(20), phone char(11), addr varchar2(50) ); insert into stu values('S0001','zhang yun','13737351256','zhengzhou'); insert into stu values('S0002','liu gang','13737347788','zhengzhou'); insert into stu values('S0003','wang tao','13737364455','zhengzhou'); insert into stu values('S0004','zhao peng','13837321477','zhengzhou'); insert into stu values('S0005','li ming','13837362444','xinxiang'); insert into stu values('S0006','wei feng','13837385666','xinxiang'); */ SQL> select * from stu; S_ID S_NAME PHONE ADDR ---------- --------------- --------------- --------------- S0006 wei feng 13837385666 xinxiang S0001 zhang yun 13737351256 zhengzhou S0002 liu gang 13737347788 zhengzhou S0003 wang tao 13737364455 zhengzhou S0004 zhao peng 13837321477 zhengzhou S0005 li ming 13837362444 xinxiang 6 rows selected. /* create table course( c_id char(5) primary key, c_name varchar2(20) ); insert into course values('C0001','English'); insert into course values('C0002','Management'); insert into course values('C0003','Ecnomics'); insert into course values('C0004','C Language'); insert into course values('C0005','OS'); insert into course values('C0006','Network'); */ SQL> select * from course; C_ID C_NAME ----- -------------------- C0001 English C0002 Management C0003 Ecnomics C0004 C Language C0005 OS C0006 Network 6 rows selected. /* create table score( s_id char(5), c_id char(5), score int, primary key(s_id, c_id) ); insert into score values('S0001','C0001',90); insert into score values('S0001','C0002',88); insert into score values('S0001','C0003',90); insert into score values('S0001','C0004',95); insert into score values('S0001','C0005',84); insert into score values('S0001','C0006',68); insert into score values('S0002','C0001',77); insert into score values('S0002','C0002',85); insert into score values('S0002','C0003',92); insert into score values('S0002','C0004',85); insert into score values('S0002','C0005',97); insert into score values('S0002','C0006',97); insert into score values('S0003','C0001',66); insert into score values('S0003','C0002',81); insert into score values('S0003','C0003',78); insert into score values('S0003','C0004',66); insert into score values('S0003','C0005',90); insert into score values('S0003','C0006',82); insert into score values('S0004','C0001',89); insert into score values('S0004','C0002',80); insert into score values('S0004','C0003',85); insert into score values('S0004','C0004',78); insert into score values('S0004','C0005',85); insert into score values('S0004','C0006',79); insert into score values('S0005','C0001',85); insert into score values('S0005','C0002',82); insert into score values('S0005','C0003',85); insert into score values('S0005','C0004',74); insert into score values('S0005','C0005',56); insert into score values('S0005','C0006',62); insert into score values('S0006','C0001',97); insert into score values('S0006','C0002',90); insert into score values('S0006','C0003',92); insert into score values('S0006','C0004',90); insert into score values('S0006','C0005',95); insert into score values('S0006','C0006',92); */ SQL> select * from score; S_ID C_ID SCORE ---------- ----- ---------- S0001 C0001 90 S0001 C0002 88 S0001 C0003 90 S0001 C0004 95 S0001 C0005 84 S0001 C0006 68 S0002 C0001 77 S0002 C0002 85 S0002 C0003 92 S0002 C0004 85 S0002 C0005 97 S0002 C0006 97 S0003 C0001 66 S0003 C0002 81 S0003 C0003 78 S0003 C0004 66 S0003 C0005 90 S0003 C0006 82 S0004 C0001 89 S0004 C0002 80 S0004 C0003 85 S0004 C0004 78 S0004 C0005 85 S0004 C0006 79 S0005 C0001 85 S0005 C0002 82 S0005 C0003 85 S0005 C0004 74 S0005 C0005 56 S0005 C0006 62 S0006 C0001 97 S0006 C0002 90 S0006 C0003 92 S0006 C0004 90 S0006 C0005 95 S0006 C0006 92 36 rows selected.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
2、row_number() 函数的用法
row_number() 函数的语法格式如下：
ROW_NUMBER() over(partition by 列名 order by 列名 [asc|desc]) alias /* 说明：row_number() 函数为每组的行设置一个连续的递增的数字（1、2、3、4、5、6） */- 1
- 2
- 3
- 4
例如：查询每个学生各门课程的成绩排名情况：
/* select s.s_id, s.s_name, c.*, score, row_number() over(partition by sc.s_id order by sc.score desc) rank from stu s join score sc on s.s_id = sc.s_id join course c on c.c_id = sc.c_id; */ select s.s_id, s.s_name, c.*, score, row_number() over(partition by sc.s_id order by sc.score desc) rank from stu s join score sc on s.s_id = sc.s_id 4 join course c on c.c_id = sc.c_id; S_ID S_NAME C_ID C_NAME SCORE RANK ---------- --------------- ----- -------------------- ---------- ---------- S0001 zhang yun C0004 C Language 95 1 S0001 zhang yun C0003 Ecnomics 90 2 S0001 zhang yun C0001 English 90 3 S0001 zhang yun C0002 Management 88 4 S0001 zhang yun C0005 OS 84 5 S0001 zhang yun C0006 Network 68 6 S0002 liu gang C0006 Network 97 1 S0002 liu gang C0005 OS 97 2 S0002 liu gang C0003 Ecnomics 92 3 S0002 liu gang C0002 Management 85 4 S0002 liu gang C0004 C Language 85 5 S0002 liu gang C0001 English 77 6 S0003 wang tao C0005 OS 90 1 S0003 wang tao C0006 Network 82 2 S0003 wang tao C0002 Management 81 3 S0003 wang tao C0003 Ecnomics 78 4 S0003 wang tao C0004 C Language 66 5 S0003 wang tao C0001 English 66 6 S0004 zhao peng C0001 English 89 1 S0004 zhao peng C0003 Ecnomics 85 2 S0004 zhao peng C0005 OS 85 3 S0004 zhao peng C0002 Management 80 4 S0004 zhao peng C0006 Network 79 5 S0004 zhao peng C0004 C Language 78 6 S0005 li ming C0003 Ecnomics 85 1 S0005 li ming C0001 English 85 2 S0005 li ming C0002 Management 82 3 S0005 li ming C0004 C Language 74 4 S0005 li ming C0006 Network 62 5 S0005 li ming C0005 OS 56 6 S0006 wei feng C0001 English 97 1 S0006 wei feng C0005 OS 95 2 S0006 wei feng C0006 Network 92 3 S0006 wei feng C0003 Ecnomics 92 4 S0006 wei feng C0002 Management 90 5 S0006 wei feng C0004 C Language 90 6 36 rows selected.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
3、rank() 函数的用法
rank() 函数的语法格式如下：
RANK() over(partition by 列名 order by 列名 [asc|desc]) alias /* 说明：rank() 用于生成排名，为每一组的行生成一个序号，如果有相同的值会生成相同的序号，并且接下来的序号是不连序的。例如：有三个人并列第一名，第四名序号为 4，生成的排名为：1、1、1、4、5、6 */- 1
- 2
- 3
- 4
例如：查询每个学生各门课程的成绩排名情况：
/* select s.s_id, s.s_name, c.*, score, rank() over(partition by sc.s_id order by sc.score desc) rank from stu s join score sc on s.s_id = sc.s_id join course c on c.c_id = sc.c_id; */ SQL> select s.s_id, s.s_name, c.*, score, rank() over(partition by sc.s_id order by sc.score desc) rank from stu s join score sc on s.s_id = sc.s_id 4 join course c on c.c_id = sc.c_id; S_ID S_NAME C_ID C_NAME SCORE RANK ---------- --------------- ----- -------------------- ---------- ---------- S0001 zhang yun C0004 C Language 95 1 S0001 zhang yun C0003 Ecnomics 90 2 S0001 zhang yun C0001 English 90 2 S0001 zhang yun C0002 Management 88 4 S0001 zhang yun C0005 OS 84 5 S0001 zhang yun C0006 Network 68 6 S0002 liu gang C0006 Network 97 1 S0002 liu gang C0005 OS 97 1 S0002 liu gang C0003 Ecnomics 92 3 S0002 liu gang C0002 Management 85 4 S0002 liu gang C0004 C Language 85 4 S0002 liu gang C0001 English 77 6 S0003 wang tao C0005 OS 90 1 S0003 wang tao C0006 Network 82 2 S0003 wang tao C0002 Management 81 3 S0003 wang tao C0003 Ecnomics 78 4 S0003 wang tao C0004 C Language 66 5 S0003 wang tao C0001 English 66 5 S0004 zhao peng C0001 English 89 1 S0004 zhao peng C0003 Ecnomics 85 2 S0004 zhao peng C0005 OS 85 2 S0004 zhao peng C0002 Management 80 4 S0004 zhao peng C0006 Network 79 5 S0004 zhao peng C0004 C Language 78 6 S0005 li ming C0003 Ecnomics 85 1 S0005 li ming C0001 English 85 1 S0005 li ming C0002 Management 82 3 S0005 li ming C0004 C Language 74 4 S0005 li ming C0006 Network 62 5 S0005 li ming C0005 OS 56 6 S0006 wei feng C0001 English 97 1 S0006 wei feng C0005 OS 95 2 S0006 wei feng C0006 Network 92 3 S0006 wei feng C0003 Ecnomics 92 3 S0006 wei feng C0002 Management 90 5 S0006 wei feng C0004 C Language 90 5 36 rows selected.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
4、dense_rank() 函数的用法
dense_rank() 函数的语法格式如下：
DENSE_RANK() over(partition by 列名 order by 列名 [asc|desc]) alias /* 说明：DENSE_RANK() 和 RANK() 类似，不同的是如果有相同的序号，那么接下来的序号连续生成。例如：有三个人并列第一，第四名序号为2，生成的排名为：1、1、1、2、3、4 */- 1
- 2
- 3
- 4
例如：
（1）查询每个学生各门课程的成绩排名情况：
/* select s.s_id, s.s_name, c.*, score, dense_rank() over(partition by sc.s_id order by sc.score desc) rank from stu s join score sc on s.s_id = sc.s_id join course c on c.c_id = sc.c_id; */ SQL> select s.s_id, s.s_name, c.*, score, dense_rank() over(partition by sc.s_id order by sc.score desc) rank from stu s join score sc on s.s_id = sc.s_id 4 join course c on c.c_id = sc.c_id; S_ID S_NAME C_ID C_NAME SCORE RANK ---------- --------------- ----- -------------------- ---------- ---------- S0001 zhang yun C0004 C Language 95 1 S0001 zhang yun C0003 Ecnomics 90 2 S0001 zhang yun C0001 English 90 2 S0001 zhang yun C0002 Management 88 3 S0001 zhang yun C0005 OS 84 4 S0001 zhang yun C0006 Network 68 5 S0002 liu gang C0006 Network 97 1 S0002 liu gang C0005 OS 97 1 S0002 liu gang C0003 Ecnomics 92 2 S0002 liu gang C0002 Management 85 3 S0002 liu gang C0004 C Language 85 3 S0002 liu gang C0001 English 77 4 S0003 wang tao C0005 OS 90 1 S0003 wang tao C0006 Network 82 2 S0003 wang tao C0002 Management 81 3 S0003 wang tao C0003 Ecnomics 78 4 S0003 wang tao C0004 C Language 66 5 S0003 wang tao C0001 English 66 5 S0004 zhao peng C0001 English 89 1 S0004 zhao peng C0003 Ecnomics 85 2 S0004 zhao peng C0005 OS 85 2 S0004 zhao peng C0002 Management 80 3 S0004 zhao peng C0006 Network 79 4 S0004 zhao peng C0004 C Language 78 5 S0005 li ming C0003 Ecnomics 85 1 S0005 li ming C0001 English 85 1 S0005 li ming C0002 Management 82 2 S0005 li ming C0004 C Language 74 3 S0005 li ming C0006 Network 62 4 S0005 li ming C0005 OS 56 5 S0006 wei feng C0001 English 97 1 S0006 wei feng C0005 OS 95 2 S0006 wei feng C0006 Network 92 3 S0006 wei feng C0003 Ecnomics 92 3 S0006 wei feng C0002 Management 90 4 S0006 wei feng C0004 C Language 90 4 36 rows selected.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
（2）查询每个学生考试成绩最高的课程信息：
/* select * from (select s.s_id, s.s_name, c.*, score, dense_rank() over(partition by sc.s_id order by sc.score desc) rank from stu s join score sc on s.s_id = sc.s_id join course c on c.c_id = sc.c_id) a where rank = 1; */ SQL> select * from (select s.s_id, s.s_name, c.*, score, dense_rank() over(partition by sc.s_id order by sc.score desc) rank from stu s join score sc on s.s_id = sc.s_id join course c on c.c_id = sc.c_id) a where rank = 1; 2 3 4 5 6 S_ID S_NAME C_ID C_NAME SCORE RANK ----- -------------------- ----- -------------------- ---------- ---------- S0001 zhang yun C0004 C Language 95 1 S0002 liu gang C0005 OS 97 1 S0002 liu gang C0006 Network 97 1 S0003 wang tao C0005 OS 90 1 S0004 zhao peng C0001 English 89 1 S0005 li ming C0001 English 85 1 S0005 li ming C0003 Ecnomics 85 1 S0006 wei feng C0001 English 97 1 8 rows selected.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
5、row_number()、rank()、dense_rank() 的比较
/* select c.c_id, c.c_name, score, row_number() over(order by sc.score desc) rownumber, rank() over(order by sc.score desc) rank, dense_rank() over(order by sc.score desc) denserank from stu s join score sc on s.s_id = sc.s_id join course c on c.c_id = sc.c_id where s.s_id = 'S0001'; */ SQL> select c.c_id, c.c_name, score, row_number() over(order by sc.score desc) rownumber, rank() over(order by sc.score desc) rank, dense_rank() over(order by sc.score desc) denserank from stu s join score sc on s.s_id = sc.s_id join 2 3 4 5 6 course c on c.c_id = sc.c_id where s.s_id = 'S0001'; 7 C_ID C_NAME SCORE ROWNUMBER RANK DENSERANK ----- -------------------- ---------- ---------- ---------- ---------- C0004 C Language 95 1 1 1 C0001 English 90 2 2 2 C0003 Ecnomics 90 3 2 2 C0002 Management 88 4 4 3 C0005 OS 84 5 5 4 C0006 Network 68 6 6 5 6 rows selected.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
三、聚合开窗函数的使用举例
over() 表示把函数当成开窗函数而不是聚合函数，SQL 标准允许将所有聚合函数用做开窗函数，使用 over 关键字来区分这两种用法。以聚合函数 sum 为例，聚合开窗函数的语法格式如下：
-- 如果 over 不带参数，对表中的所有数据求和 sum(列名) over() -- 如果 over 带 partition by 参数，根据【列名2】分组，对每一组数据求和 sum(列名1) over(partition by 列名2) -- 如果 over 带 order by 参数，则根据【列名2】排序，对表中的数据进行累加 sum(列名1) over(order by 列名2 asc|desc) -- 如果 over 既带 partition by 参数，又带 order by 参数，则根据【列名2】分组，然后针对每一组根据【列名3】排序，对表中每一组数据进行累加 sum(列名1) over(partition by 列名2 order by 列名3 asc|desc)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
1、创建表，并添加数据
/* create table employee as select empno, ename, sal, deptno from emp; */ SQL> select * from employee; EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ---------- 7369 SMITH 800 20 7499 ALLEN 1600 30 7521 WARD 1250 30 7566 JONES 2975 20 7654 MARTIN 1250 30 7698 BLAKE 2850 30 7782 CLARK 2450 10 7788 SCOTT 3000 20 7839 KING 5000 10 7844 TURNER 1500 30 7876 ADAMS 1100 20 7900 JAMES 950 30 7902 FORD 3000 20 7934 MILLER 1300 10 14 rows selected.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
2、对表中的所有数据求和（over 没有参数）
SQL> select empno, ename, sal, sum(sal) over() total from employee; EMPNO ENAME SAL TOTAL ---------- ---------- ---------- ---------- 7369 SMITH 800 29025 7499 ALLEN 1600 29025 7521 WARD 1250 29025 7566 JONES 2975 29025 7654 MARTIN 1250 29025 7698 BLAKE 2850 29025 7782 CLARK 2450 29025 7788 SCOTT 3000 29025 7839 KING 5000 29025 7844 TURNER 1500 29025 7876 ADAMS 1100 29025 7900 JAMES 950 29025 7902 FORD 3000 29025 7934 MILLER 1300 29025 14 rows selected.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
3、对表中的数据分组求和（over(partition by 列名)）
/* select empno, ename, sal, deptno, sum(sal) over(partition by deptno) total from employee; */ SQL> select empno, ename, sal, deptno, sum(sal) over(partition by deptno) total from employee; 2 3 EMPNO ENAME SAL DEPTNO TOTAL ---------- ---------- ---------- ---------- ---------- 7782 CLARK 2450 10 8750 7839 KING 5000 10 8750 7934 MILLER 1300 10 8750 7566 JONES 2975 20 10875 7902 FORD 3000 20 10875 7876 ADAMS 1100 20 10875 7369 SMITH 800 20 10875 7788 SCOTT 3000 20 10875 7521 WARD 1250 30 9400 7844 TURNER 1500 30 9400 7499 ALLEN 1600 30 9400 7900 JAMES 950 30 9400 7698 BLAKE 2850 30 9400 7654 MARTIN 1250 30 9400 14 rows selected.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
4、对表中的数据进行累加（over(order by 列名)）
/* select empno, ename, sal, deptno, sum(sal) over(order by empno desc) total from employee; */ SQL> select empno, ename, sal, deptno, sum(sal) over(order by empno desc) total from employee; 2 3 EMPNO ENAME SAL DEPTNO TOTAL ---------- ---------- ---------- ---------- ---------- 7934 MILLER 1300 10 1300 7902 FORD 3000 20 4300 7900 JAMES 950 30 5250 7876 ADAMS 1100 20 6350 7844 TURNER 1500 30 7850 7839 KING 5000 10 12850 7788 SCOTT 3000 20 15850 7782 CLARK 2450 10 18300 7698 BLAKE 2850 30 21150 7654 MARTIN 1250 30 22400 7566 JONES 2975 20 25375 7521 WARD 1250 30 26625 7499 ALLEN 1600 30 28225 7369 SMITH 800 20 29025 14 rows selected.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
5、对表中的数据分组并进行累加（over(partition 列名1 order by 列名2)）
/* select empno, ename, sal, deptno, sum(sal) over(partition by deptno order by empno) total from employee; */ SQL> select empno, ename, sal, deptno, sum(sal) over(partition by deptno order by empno) total from employee; 2 3 EMPNO ENAME SAL DEPTNO TOTAL ---------- ---------- ---------- ---------- ---------- 7782 CLARK 2450 10 2450 7839 KING 5000 10 7450 7934 MILLER 1300 10 8750 7369 SMITH 800 20 800 7566 JONES 2975 20 3775 7788 SCOTT 3000 20 6775 7876 ADAMS 1100 20 7875 7902 FORD 3000 20 10875 7499 ALLEN 1600 30 1600 7521 WARD 1250 30 2850 7654 MARTIN 1250 30 4100 7698 BLAKE 2850 30 6950 7844 TURNER 1500 30 8450 7900 JAMES 950 30 9400 14 rows selected.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
6、例子
（1）查询工资高于本部门平均工资的员工信息
/* select empno, ename, sal, deptno from (select empno, ename, sal, deptno, avg(sal) over(partition by deptno) avg_sal from employee) a where sal > avg_sal; */ SQL> select empno, ename, sal, deptno from (select empno, ename, sal, deptno, avg(sal) over(partition by deptno) avg_sal from employee) a where sal > avg_sal; 2 3 4 5 6 EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ---------- 7839 KING 5000 10 7566 JONES 2975 20 7902 FORD 3000 20 7788 SCOTT 3000 20 7499 ALLEN 1600 30 7698 BLAKE 2850 30 6 rows selected.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
（2）查询每个部门工资最高的员工信息
/* select empno, ename, sal, deptno from (select empno, ename, sal, deptno, max(sal) over(partition by deptno) max_sal from employee) a where sal = max_sal; */ SQL> select empno, ename, sal, deptno from (select empno, ename, sal, deptno, max(sal) over(partition by deptno) max_sal from employee) a where sal = max_sal; 2 3 4 5 6 EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ---------- 7839 KING 5000 10 7902 FORD 3000 20 7788 SCOTT 3000 20 7698 BLAKE 2850 30- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
-
相关阅读:
减少无线链接切换导致数据体验变差的技术简介
爬虫（1） - 爬虫基础入门理论篇
SPARKSQL3.0-Catalog源码剖析
es6~ 常用语法总结
多模态MLLM都是怎么实现的(9)-时序LLM是怎么个事儿？
《canvas》之第11章 canvas状态
python 基于http方式与基于redis方式传输摄像头图片数据的实现和对比
Hadoop3教程（一）：Hadoop的定义、组成及全生态概览
传统IT人的崩溃瞬间……
5.无霍尔BLDC转子预定位及同步加速
- 原文地址：https://blog.csdn.net/weixin_44377973/article/details/126226248
