語法：
ROW_NUMBER() OVER( [ PRITITION BY col1] ORDER BY col2[ DESC ] )
解釋：
ROW_NUMBER()為返回的記錄定義個行編號， PARTITION BY col1 是根據(jù)col1分組，ORDER BY col2[ DESC ]是根據(jù)col2進行排序。
舉例：
postgres=# create table student(id serial,name character varying,course character varying,score integer);
CREATE TABLE
postgres=#
postgres=# \d student
Table "public.student"
Column | Type | Modifiers
--------+-------------------+----------------------------------------------
id | integer | not null default nextval('student_id_seq'::regclass)
name | character varying |
course | character varying |
score | integer |
insert into student (name,course,score) values('周潤發(fā)','語文',89);
insert into student (name,course,score) values('周潤發(fā)','數(shù)學(xué)',99);
insert into student (name,course,score) values('周潤發(fā)','外語',67);
insert into student (name,course,score) values('周潤發(fā)','物理',77);
insert into student (name,course,score) values('周潤發(fā)','化學(xué)',87);
insert into student (name,course,score) values('周星馳','語文',91);
insert into student (name,course,score) values('周星馳','數(shù)學(xué)',81);
insert into student (name,course,score) values('周星馳','外語',88);
insert into student (name,course,score) values('周星馳','物理',68);
insert into student (name,course,score) values('周星馳','化學(xué)',83);
insert into student (name,course,score) values('黎明','語文',85);
insert into student (name,course,score) values('黎明','數(shù)學(xué)',65);
insert into student (name,course,score) values('黎明','外語',95);
insert into student (name,course,score) values('黎明','物理',90);
insert into student (name,course,score) values('黎明','化學(xué)',78);
1. 根據(jù)分數(shù)排序
postgres=# select *,row_number() over(order by score desc)rn from student;
id | name | course | score | rn
----+--------+--------+-------+----
2 | 周潤發(fā) | 數(shù)學(xué) | 99 | 1
13 | 黎明 | 外語 | 95 | 2
6 | 周星馳 | 語文 | 91 | 3
14 | 黎明 | 物理 | 90 | 4
1 | 周潤發(fā) | 語文 | 89 | 5
8 | 周星馳 | 外語 | 88 | 6
5 | 周潤發(fā) | 化學(xué) | 87 | 7
11 | 黎明 | 語文 | 85 | 8
10 | 周星馳 | 化學(xué) | 83 | 9
7 | 周星馳 | 數(shù)學(xué) | 81 | 10
15 | 黎明 | 化學(xué) | 78 | 11
4 | 周潤發(fā) | 物理 | 77 | 12
9 | 周星馳 | 物理 | 68 | 13
3 | 周潤發(fā) | 外語 | 67 | 14
12 | 黎明 | 數(shù)學(xué) | 65 | 15
(15 rows)
rn是給我們的一個排序。
2. 根據(jù)科目分組，按分數(shù)排序
postgres=# select *,row_number() over(partition by course order by score desc)rn from student;
id | name | course | score | rn
----+--------+--------+-------+----
5 | 周潤發(fā) | 化學(xué) | 87 | 1
10 | 周星馳 | 化學(xué) | 83 | 2
15 | 黎明 | 化學(xué) | 78 | 3
13 | 黎明 | 外語 | 95 | 1
8 | 周星馳 | 外語 | 88 | 2
3 | 周潤發(fā) | 外語 | 67 | 3
2 | 周潤發(fā) | 數(shù)學(xué) | 99 | 1
7 | 周星馳 | 數(shù)學(xué) | 81 | 2
12 | 黎明 | 數(shù)學(xué) | 65 | 3
14 | 黎明 | 物理 | 90 | 1
4 | 周潤發(fā) | 物理 | 77 | 2
9 | 周星馳 | 物理 | 68 | 3
6 | 周星馳 | 語文 | 91 | 1
1 | 周潤發(fā) | 語文 | 89 | 2
11 | 黎明 | 語文 | 85 | 3
(15 rows)
3. 獲取每個科目的最高分
postgres=# select * from(select *,row_number() over(partition by course order by score desc)rn from student)t where rn=1;
id | name | course | score | rn
----+--------+--------+-------+----
5 | 周潤發(fā) | 化學(xué) | 87 | 1
13 | 黎明 | 外語 | 95 | 1
2 | 周潤發(fā) | 數(shù)學(xué) | 99 | 1
14 | 黎明 | 物理 | 90 | 1
6 | 周星馳 | 語文 | 91 | 1
(5 rows)
4. 每個科目的最低分也是一樣的
postgres=# select * from(select *,row_number() over(partition by course order by score)rn from student)t where rn=1;
id | name | course | score | rn
----+--------+--------+-------+----
15 | 黎明 | 化學(xué) | 78 | 1
3 | 周潤發(fā) | 外語 | 67 | 1
12 | 黎明 | 數(shù)學(xué) | 65 | 1
9 | 周星馳 | 物理 | 68 | 1
11 | 黎明 | 語文 | 85 | 1
(5 rows)
只要在根據(jù)科目排序的時候按低到高順序排列就好了。
補充：SQL：postgresql中為查詢結(jié)果增加一個自增序列之ROW_NUMBER () OVER ()的使用
舉例說明:
SELECT ROW_NUMBER
() OVER ( ORDER BY starttime DESC ) "id",
starttime AS "text",
starttime
FROM
warning_products
WHERE
pid_model = '結(jié)果'
AND starttime IS NOT NULL
GROUP BY
starttime
在這一段代碼中:
查詢語句就不說了, select …from…where
GROUP BY的作用:
這一段代碼執(zhí)行的結(jié)果是:
如果將GROUP BY刪除,那么執(zhí)行結(jié)果為:
可以看到查詢出了兩個相同starttime數(shù)據(jù).
由此得出:
GROUP BY的作用是分類匯總.也就是說,查詢結(jié)果中,starttime每一種查詢結(jié)果只有一個
GROUP BY的作用:
如果將DESC換成
() OVER ( ORDER BY starttime ASC ) "id",
則運行結(jié)果為:
相比可以發(fā)現(xiàn),ORDER BY的作用為進行排序.
按照某種要求進行固定的排序
ROW_NUMBER () OVER() “id”
先來看一下,如果把這一段刪掉,運行結(jié)果:
加上呢?
明顯的對比,我們?yōu)樽罱K的查詢結(jié)果增加了一列自增的id序列(這里id可以改名,"id"改為其他的即可)
由此得到結(jié)論,在執(zhí)行帶有row_number() over() "xx"的SQL語句時候,代碼會先執(zhí)行查詢語句,然后執(zhí)行over中的命令,最后為結(jié)果增加一列自增的序列.
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- MYSQL row_number()與over()函數(shù)用法詳解
- postgreSQL中的row_number() 與distinct用法說明
- postgresql rank() over, dense_rank(), row_number()用法區(qū)別
- MySQL中row_number的實現(xiàn)過程
- SQL Server中row_number函數(shù)的常見用法示例詳解
- sql四大排名函數(shù)之ROW_NUMBER、RANK、DENSE_RANK、NTILE使用介紹
- sql ROW_NUMBER()與OVER()方法案例詳解
