oracle over()的使用和需要特別注意的地方
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.itpub.net/23135684/viewspace-682083/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- BigDecimal使用注意的地方Decimal
- 離職那些需要注意的地方
- distinct 去重需要注意的地方
- 使用MVCPager做AJAX分頁所需要注意的地方MVC
- equals中關於空格需要注意的地方
- SAP打補丁時需要注意的地方
- 【Redis】redis-cluster需要注意的幾個地方Redis
- Go 1.16 中關於 go get 和 go install 你需要注意的地方Go
- C++ auto 型別推斷注意的地方C++型別
- 蘋果企業開發者賬號的使用需要注意哪些地方蘋果
- cmake混編.c和.cpp需要注意的地方--指定兩種編譯器編譯
- 開發網路影片直播系統需要注意的地方
- RationalDMIS7.1 定義校驗規需要注意的地方
- 選擇雲伺服器有哪些需要注意的地方？伺服器
- 進行爬蟲的時候需要注意哪些地方？爬蟲
- Python中Lambda使用要注意的幾個地方Python
- oracle設定process需要注意的事情Oracle
- Java基礎系列（三十六）：泛型中需要注意的地方Java泛型
- 箭頭函式適用場景及需要注意的地方函式
- 平臺接入美顏SDK工具有哪些需要注意的地方？
- 藍橋杯注意的地方
- Oracle使用*的注意事項Oracle
- Oracle remap_schema需要注意的問題OracleREM
- 一個優秀的雲原生架構需要注意哪些地方架構
- 微信企業付款介面PHP開發需要注意的兩個地方PHP
- 使用 sendBeacon 需要注意的問題
- jQuery中使用$.each()遍歷陣列時要注意的地方jQuery陣列
- 建設個性化主題多媒體展廳需要注意的地方
- 記錄使用 Homestead 需要注意的事情
- 使用 foreach 使用引用變數需要注意的問題變數
- mysql自動排序函式dense_rank() over()、rank() over()、row_num() over()用法和區別MySql排序函式
- MySQL和Oracle的區別MySqlOracle
- Oracle和MySQL的區別OracleMySql
- 藍橋杯——特別數的和
- 你認為一個好的佈局應該是什麼樣的？有哪些需要注意的地方？
- 軟體進行相容性測試需要注意哪些地方?
- golang defer使用需要注意Golang
- oracle資料庫CPU特別高的解決方法Oracle資料庫
- Laravel 使用 PostgreSQL 資料庫需要注意的點LaravelSQL資料庫