|
STUDENT表数据。
SQL> select * from students;
ID CLASS NAME AGE COURSE SCORE
---------- ---------- ---------- ---------- ---------- ----------
325 三班 张1 23 英语 67
326 二班 李2 26 语文 47
327 三班 刘2 22 数学 87
328 四班 常1 22 自然 99
329 一班 张3 23 英语 77
330 三班 黄1 24 数学 97
331 三班 田1 28 数学 87
332 四班 达1 22 自然 97
333 三班 叶1 26 英语 67
334 四班 胖1 26 语文 94
335 三班 虎1 28 数学 77
ID CLASS NAME AGE COURSE SCORE
---------- ---------- ---------- ---------- ---------- ----------
336 四班 水1 22 自然 93
337 一班 韬1 23 英语 62
338 二班 李1 26 语文 97
339 三班 辜1 28 数学 85
340 一班 喻1 22 自然 92
341 四班 杨1 23 英语 61
342 二班 凯1 26 语文 95
343 三班 子1 24 数学 84
344 四班 万1 22 自然 97
345 一班 丹1 23 英语 68
346 二班 小1 26 语文 93
ID CLASS NAME AGE COURSE SCORE
---------- ---------- ---------- ---------- ---------- ----------
347 三班 白1 25 数学 82
348 四班 钟1 22 自然 94
349 一班 宇1 23 英语 62
350 三班 帅1 24 语文 92
351 三班 男1 23 数学 87
352 四班 我1 22 自然 94
353 一班 你1 23 英语 57
354 一班 阿1 26 语文 87
355 三班 刘1 24 数学 67
356 四班 常1 21 自然 96
357 二班 秋1 23 英语 77
ID CLASS NAME AGE COURSE SCORE
---------- ---------- ---------- ---------- ---------- ----------
358 二班 饿2 26 语文 87
359 二班 胡2 22 数学 77
360 二班 可1 22 自然 69
36 rows selected.
STUDENT表结构。
SQL> desc students
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
CLASS VARCHAR2(20)
NAME VARCHAR2(20)
AGE NUMBER
COURSE VARCHAR2(20)
SCORE NUMBER
1.查询出每个班每个学科的第一名。
SQL> select class,name,course,score from (select class,name,course,score,row_number() over (partition by class,course order by class,course,score) rn from students) where rn=1;
CLASS NAME COURSE SCORE
---------- ---------- ---------- ----------
二班 胡2 数学 77
二班 秋1 英语 77
二班 李2 语文 47
二班 可1 自然 69
三班 刘1 数学 67
三班 张1 英语 67
三班 帅1 语文 92
四班 杨1 英语 61
四班 胖1 语文 94
四班 水1 自然 93
一班 你1 英语 57
CLASS NAME COURSE SCORE
---------- ---------- ---------- ----------
一班 阿1 语文 87
一班 喻1 自然 92
13 rows selected.
2.row_number()用法,按照CLASS和COURSE进行分组，每组内部进行编号。
SQL> select class,name,course,score,row_number() over (partition by class,course order by class,course,score) rn from students;
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
二班 胡2 数学 77 1
二班 秋1 英语 77 1
二班 李2 语文 47 1
二班 饿2 语文 87 2
二班 小1 语文 93 3
二班 凯1 语文 95 4
二班 李1 语文 97 5
二班 可1 自然 69 1
三班 刘1 数学 67 1
三班 虎1 数学 77 2
三班 白1 数学 82 3
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
三班 子1 数学 84 4
三班 辜1 数学 85 5
三班 刘2 数学 87 6
三班 男1 数学 87 7
三班 田1 数学 87 8
三班 黄1 数学 97 9
三班 叶1 英语 67 1
三班 张1 英语 67 2
三班 帅1 语文 92 1
四班 杨1 英语 61 1
四班 胖1 语文 94 1
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
四班 水1 自然 93 1
四班 我1 自然 94 2
四班 钟1 自然 94 3
四班 常1 自然 96 4
四班 达1 自然 97 5
四班 万1 自然 97 6
四班 常1 自然 99 7
一班 你1 英语 57 1
一班 韬1 英语 62 2
一班 宇1 英语 62 3
一班 丹1 英语 68 4
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
一班 张3 英语 77 5
一班 阿1 语文 87 1
一班 喻1 自然 92 1
36 rows selected.
3.row_number()用法，按照score进行排序，按照排序进行编号，跟rownum相同。
SQL> select class,name,course,score,row_number() over (order by score) rn from students where course='数学';
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
三班 刘1 数学 67 1
三班 虎1 数学 77 2
二班 胡2 数学 77 3
三班 白1 数学 82 4
三班 子1 数学 84 5
三班 辜1 数学 85 6
三班 男1 数学 87 7
三班 刘2 数学 87 8
三班 田1 数学 87 9
三班 黄1 数学 97 10
10 rows selected.
4.rank()用法，按照score进行排序，score相同的值并列相同编号，后面的值顺延排序。
SQL> select class,name,course,score,rank() over (order by score) rn from students where course='数学';
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
三班 刘1 数学 67 1
三班 虎1 数学 77 2
二班 胡2 数学 77 2
三班 白1 数学 82 4
三班 子1 数学 84 5
三班 辜1 数学 85 6
三班 男1 数学 87 7
三班 刘2 数学 87 7
三班 田1 数学 87 7
三班 黄1 数学 97 10
10 rows selected.
5.dense_rank()用法，按照score进行排序，socre相同的值并列相同编号，后面的值紧接着进行排序。
SQL> select class,name,course,score,dense_rank() over (order by score) rn from students where course='数学';
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
三班 刘1 数学 67 1
三班 虎1 数学 77 2
二班 胡2 数学 77 2
三班 白1 数学 82 3
三班 子1 数学 84 4
三班 辜1 数学 85 5
三班 男1 数学 87 6
三班 刘2 数学 87 6
三班 田1 数学 87 6
三班 黄1 数学 97 7
10 rows selected.
6.max() over()用法，对比以下2种不同的写法，只是在order by上有差别，从这2个例子可以看出，max是按照order by 的列来求最大值的，而不是按照partition by的列来求的最大值，如果没有order by 那么就是按照partition by的列来求的最大值。(注意：分组依然是按照partition by进行分组，只是在进行聚集计算的时候是按照order by来分组计算的)。
例子1：
SQL> select class,name,course,score,max(score) over (partition by class,course order by class,course,score) rn from students;
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
二班 胡2 数学 77 77
二班 秋1 英语 77 77
二班 李2 语文 47 47
二班 饿2 语文 87 87
二班 小1 语文 93 93
二班 凯1 语文 95 95
二班 李1 语文 97 97
二班 可1 自然 69 69
三班 刘1 数学 67 67
三班 虎1 数学 77 77
三班 白1 数学 82 82
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
三班 子1 数学 84 84
三班 辜1 数学 85 85
三班 刘2 数学 87 87
三班 男1 数学 87 87
三班 田1 数学 87 87
三班 黄1 数学 97 97
三班 叶1 英语 67 67
三班 张1 英语 67 67
三班 帅1 语文 92 92
四班 杨1 英语 61 61
四班 胖1 语文 94 94
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
四班 水1 自然 93 93
四班 我1 自然 94 94
四班 钟1 自然 94 94
四班 常1 自然 96 96
四班 达1 自然 97 97
四班 万1 自然 97 97
四班 常1 自然 99 99
一班 你1 英语 57 57
一班 韬1 英语 62 62
一班 宇1 英语 62 62
一班 丹1 英语 68 68
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
一班 张3 英语 77 77
一班 阿1 语文 87 87
一班 喻1 自然 92 92
36 rows selected.
以上的SQL这样写也是可以的：
按照class,course来分组，按照分组后的score分数来进行排序和按照分组后的score来进行聚集计算。
select class,name,course,score,max(score) over (partition by class,course order by score) rn from students;
例子2：
SQL> select class,name,course,score,max(score) over (partition by class,course order by class,course ) rn from students;
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
二班 胡2 数学 77 77
二班 秋1 英语 77 77
二班 李2 语文 47 97
二班 饿2 语文 87 97
二班 小1 语文 93 97
二班 凯1 语文 95 97
二班 李1 语文 97 97
二班 可1 自然 69 69
三班 刘2 数学 87 97
三班 田1 数学 87 97
三班 虎1 数学 77 97
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
三班 男1 数学 87 97
三班 白1 数学 82 97
三班 辜1 数学 85 97
三班 子1 数学 84 97
三班 黄1 数学 97 97
三班 刘1 数学 67 97
三班 叶1 英语 67 67
三班 张1 英语 67 67
三班 帅1 语文 92 92
四班 杨1 英语 61 61
四班 胖1 语文 94 94
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
四班 常1 自然 99 99
四班 万1 自然 97 99
四班 我1 自然 94 99
四班 常1 自然 96 99
四班 达1 自然 97 99
四班 水1 自然 93 99
四班 钟1 自然 94 99
一班 你1 英语 57 77
一班 韬1 英语 62 77
一班 宇1 英语 62 77
一班 丹1 英语 68 77
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
一班 张3 英语 77 77
一班 阿1 语文 87 87
一班 喻1 自然 92 92
36 rows selected.
例子3：
SQL> select class,name,course,score,max(score) over (partition by class,course ) rn from students;
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
二班 胡2 数学 77 77
二班 秋1 英语 77 77
二班 李2 语文 47 97
二班 饿2 语文 87 97
二班 小1 语文 93 97
二班 凯1 语文 95 97
二班 李1 语文 97 97
二班 可1 自然 69 69
三班 刘2 数学 87 97
三班 田1 数学 87 97
三班 虎1 数学 77 97
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
三班 男1 数学 87 97
三班 白1 数学 82 97
三班 辜1 数学 85 97
三班 子1 数学 84 97
三班 黄1 数学 97 97
三班 刘1 数学 67 97
三班 叶1 英语 67 67
三班 张1 英语 67 67
三班 帅1 语文 92 92
四班 杨1 英语 61 61
四班 胖1 语文 94 94
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
四班 常1 自然 99 99
四班 万1 自然 97 99
四班 我1 自然 94 99
四班 常1 自然 96 99
四班 达1 自然 97 99
四班 水1 自然 93 99
四班 钟1 自然 94 99
一班 你1 英语 57 77
一班 韬1 英语 62 77
一班 宇1 英语 62 77
一班 丹1 英语 68 77
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
一班 张3 英语 77 77
一班 阿1 语文 87 87
一班 喻1 自然 92 92
36 rows selected.
7.count() over()用法，跟max() over()用法差不多，也是优先根据order by来进行count，没有order by 子句再根据partition by来count的。
SQL> select class,name,course,score,count(*) over (partition by class,course ) rn from students;
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
二班 胡2 数学 77 1
二班 秋1 英语 77 1
二班 李2 语文 47 5
二班 饿2 语文 87 5
二班 小1 语文 93 5
二班 凯1 语文 95 5
二班 李1 语文 97 5
二班 可1 自然 69 1
三班 刘2 数学 87 9
三班 田1 数学 87 9
三班 虎1 数学 77 9
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
三班 男1 数学 87 9
三班 白1 数学 82 9
三班 辜1 数学 85 9
三班 子1 数学 84 9
三班 黄1 数学 97 9
三班 刘1 数学 67 9
三班 叶1 英语 67 2
三班 张1 英语 67 2
三班 帅1 语文 92 1
四班 杨1 英语 61 1
四班 胖1 语文 94 1
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
四班 常1 自然 99 7
四班 万1 自然 97 7
四班 我1 自然 94 7
四班 常1 自然 96 7
四班 达1 自然 97 7
四班 水1 自然 93 7
四班 钟1 自然 94 7
一班 你1 英语 57 5
一班 韬1 英语 62 5
一班 宇1 英语 62 5
一班 丹1 英语 68 5
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
一班 张3 英语 77 5
一班 阿1 语文 87 1
一班 喻1 自然 92 1
36 rows selected.
8.sum() over()用法，请注意观察，第1个例子如果score值是相同的，那么sum后的值也是相同的，score值相同对应的rn是想将相同的score值求了sum之后再在相同score的列对显示统一累加后的值。而第二个例子中over(class,course,score,rownum)加了rownum关键字，即使score相同但是rownum是不同的(order by class,course,score,rownum表示了唯一性)，那么就不会将相同的值全部累加之后统一显示了。例子3只order by了class和course，在rn列中就是按照class和course分组来显示sum的。通过这3个例子可以再次说明sum(),count(),max(),min()等聚合函数在有order by子句的时候不是根据partition by分组进行聚集操作的，而是根据order by 列来进行聚集操作的。(注意：分组依然是按照partition by进行分组，只是在进行聚集计算的时候是按照order by来分组计算的)。
例子1：
SQL> select class,name,course,score,sum(score) over (partition by class,course order by class,course,score) rn from students;
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
二班 胡2 数学 77 77
二班 秋1 英语 77 77
二班 李2 语文 47 47
二班 饿2 语文 87 134
二班 小1 语文 93 227
二班 凯1 语文 95 322
二班 李1 语文 97 419
二班 可1 自然 69 69
三班 刘1 数学 67 67
三班 虎1 数学 77 144
三班 白1 数学 82 226
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
三班 子1 数学 84 310
三班 辜1 数学 85 395
三班 刘2 数学 87 656
三班 男1 数学 87 656
三班 田1 数学 87 656
三班 黄1 数学 97 753
三班 叶1 英语 67 134
三班 张1 英语 67 134
三班 帅1 语文 92 92
四班 杨1 英语 61 61
四班 胖1 语文 94 94
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
四班 水1 自然 93 93
四班 我1 自然 94 281
四班 钟1 自然 94 281
四班 常1 自然 96 377
四班 达1 自然 97 571
四班 万1 自然 97 571
四班 常1 自然 99 670
一班 你1 英语 57 57
一班 韬1 英语 62 181
一班 宇1 英语 62 181
一班 丹1 英语 68 249
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
一班 张3 英语 77 326
一班 阿1 语文 87 87
一班 喻1 自然 92 92
36 rows selected.
例子2：
SQL> select class,name,course,score,sum(score) over (partition by class,course order by class,course,score,rownum ) rn from students;
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
二班 胡2 数学 77 77
二班 秋1 英语 77 77
二班 李2 语文 47 47
二班 饿2 语文 87 134
二班 小1 语文 93 227
二班 凯1 语文 95 322
二班 李1 语文 97 419
二班 可1 自然 69 69
三班 刘1 数学 67 67
三班 虎1 数学 77 144
三班 白1 数学 82 226
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
三班 子1 数学 84 310
三班 辜1 数学 85 395
三班 刘2 数学 87 482
三班 田1 数学 87 569
三班 男1 数学 87 656
三班 黄1 数学 97 753
三班 张1 英语 67 67
三班 叶1 英语 67 134
三班 帅1 语文 92 92
四班 杨1 英语 61 61
四班 胖1 语文 94 94
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
四班 水1 自然 93 93
四班 钟1 自然 94 187
四班 我1 自然 94 281
四班 常1 自然 96 377
四班 达1 自然 97 474
四班 万1 自然 97 571
四班 常1 自然 99 670
一班 你1 英语 57 57
一班 韬1 英语 62 119
一班 宇1 英语 62 181
一班 丹1 英语 68 249
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
一班 张3 英语 77 326
一班 阿1 语文 87 87
一班 喻1 自然 92 92
36 rows selected.
例子3：
SQL> select class,name,course,score,sum(score) over (partition by class,course order by class,course ) rn from students;
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
二班 胡2 数学 77 77
二班 秋1 英语 77 77
二班 李2 语文 47 419
二班 饿2 语文 87 419
二班 小1 语文 93 419
二班 凯1 语文 95 419
二班 李1 语文 97 419
二班 可1 自然 69 69
三班 刘2 数学 87 753
三班 田1 数学 87 753
三班 虎1 数学 77 753
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
三班 男1 数学 87 753
三班 白1 数学 82 753
三班 辜1 数学 85 753
三班 子1 数学 84 753
三班 黄1 数学 97 753
三班 刘1 数学 67 753
三班 叶1 英语 67 134
三班 张1 英语 67 134
三班 帅1 语文 92 92
四班 杨1 英语 61 61
四班 胖1 语文 94 94
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
四班 常1 自然 99 670
四班 万1 自然 97 670
四班 我1 自然 94 670
四班 常1 自然 96 670
四班 达1 自然 97 670
四班 水1 自然 93 670
四班 钟1 自然 94 670
一班 你1 英语 57 326
一班 韬1 英语 62 326
一班 宇1 英语 62 326
一班 丹1 英语 68 326
CLASS NAME COURSE SCORE RN
---------- ---------- ---------- ---------- ----------
一班 张3 英语 77 326
一班 阿1 语文 87 87
一班 喻1 自然 92 92
36 rows selected.
来自 “ ITPUB博客 ” ，链接：http://blog./23135684/viewspace-682083/，如需转载，请注明出处，否则将追究法律责任。
|
