PostgreSQL ROW_NUMBER() OVER()

PostgreSQL ROW_NUMBER() OVER()

• 创建表

CREATE TABLE student (ID serial,NAME CHARACTER VARYING,course CHARACTER VARYING,score INTEGER);

• 插入数据

INSERT INTO student (NAME,course,score) VALUES ('周润发','语文',89);
INSERT INTO student (NAME,course,score) VALUES ('周润发','数学',99);
INSERT INTO student (NAME,course,score) VALUES ('周润发','外语',67);
INSERT INTO student (NAME,course,score) VALUES ('周润发','物理',77);
INSERT INTO student (NAME,course,score) VALUES ('周润发','化学',87);
INSERT INTO student (NAME,course,score) VALUES ('周星驰','语文',91);
INSERT INTO student (NAME,course,score) VALUES ('周星驰','数学',81);
INSERT INTO student (NAME,course,score) VALUES ('周星驰','外语',88);
INSERT INTO student (NAME,course,score) VALUES ('周星驰','物理',68);
INSERT INTO student (NAME,course,score) VALUES ('周星驰','化学',83);
INSERT INTO student (NAME,course,score) VALUES ('黎明','语文',85);
INSERT INTO student (NAME,course,score) VALUES ('黎明','数学',65);
INSERT INTO student (NAME,course,score) VALUES ('黎明','外语',95);
INSERT INTO student (NAME,course,score) VALUES ('黎明','物理',90);
INSERT INTO student (NAME,course,score) VALUES ('黎明','化学',78);

• 1. 根据分数排序

SELECT *,ROW_NUMBER () OVER (ORDER BY score DESC) rn FROM student;

• 2. 根据科目分组，按分数排序

SELECT *,ROW_NUMBER () OVER (PARTITION BY course ORDER BY score DESC) rn FROM student;

• 3. 获取每个科目的最高分

SELECT * FROM (
SELECT *,ROW_NUMBER () OVER (PARTITION BY course ORDER BY score DESC) rn FROM student) T WHERE rn=1;

• 4. 每个科目的最低分也是一样的

SELECT * FROM (
SELECT *,ROW_NUMBER () OVER (PARTITION BY course ORDER BY score) rn FROM student) T WHERE rn=1;