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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- js switch語句需要特別注意的地方JS
- Linux下的crontab命令使用特別須注意的地方Linux
- margin-top使用需要注意的地方
- 對Oracle View授權和where查詢需要注意的地方OracleView
- springMVC的@ResponseBody、@RequestBody使用需要注意的地方SpringMVC
- oracle 11.2.0.3 版本 vote盤需要注意的地方Oracle
- 在PHP中使用類可能需要注意的地方PHP
- delete與delete[]需要注意的地方delete
- 行內元素和塊計元素需要注意的地方
- 使用Context建立一個View需要注意的地方ContextView
- /etc/fstab檔案需要注意的地方
- 測試使用字串型別要注意的地方字串型別
- hadoop中Combiner使用中需要注意的地方Hadoop
- 基本型別、字串該注意的地方型別字串
- link流程 建立時需要注意的地方
- 26個提升java效能需要注意的地方Java
- Struts中上傳檔案需要注意的地方
- 蘋果企業開發者賬號的使用需要注意哪些地方蘋果
- 用decode和nvl處理null值時需要注意的地方Null
- 【Redis】redis-cluster需要注意的幾個地方Redis
- javascript原型繼承constructor需要注意的地方JavaScript原型繼承Struct
- javascript變數宣告需要注意的一個地方JavaScript變數
- java打包exe程式需要注意的幾個地方Java
- 伺服器搬遷需要注意的幾個地方伺服器
- Python安裝中需要特別注意的一件事Python
- 使用React 應當注意的幾個地方React
- Nagois監控oracle資料庫注意的地方GoOracle資料庫
- 選擇雲伺服器有哪些需要注意的地方？伺服器
- 開發網路影片直播系統需要注意的地方
- 進行爬蟲的時候需要注意哪些地方？爬蟲
- Android EditText 的使用及值得注意的地方Android
- jquery獲取ajax傳遞的值一個需要注意的地方jQuery
- Go 1.16 中關於 go get 和 go install 你需要注意的地方Go
- 使用rman建立standby db,資料檔案目錄結構不同需要注意的地方
- 箭頭函式適用場景及需要注意的地方函式
- Java基礎系列（三十六）：泛型中需要注意的地方Java泛型
- 平臺接入美顏SDK工具有哪些需要注意的地方？
- PHP json_encode函式中需要注意的地方PHPJSON函式