ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

原创转载请注明出处：http://agilestyle.iteye.com/blog/2357284

 

ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

create table

CREATE TABLE EMPLOYEE
(
  EMPID INTEGER PRIMARY KEY,
  DEPTID INTEGER,
  SALARY DECIMAL(10,2)
);

 

insert data

INSERT INTO EMPLOYEE VALUES(1, 10, 5500.00);
INSERT INTO EMPLOYEE VALUES(2, 10, 4500.00);
INSERT INTO EMPLOYEE VALUES(3, 10, 6500.00);
INSERT INTO EMPLOYEE VALUES(4, 20, 1900.00);
INSERT INTO EMPLOYEE VALUES(5, 20, 4800.00);
INSERT INTO EMPLOYEE VALUES(6, 20, 5800.00);
INSERT INTO EMPLOYEE VALUES(7, 40, 6500.00);
INSERT INTO EMPLOYEE VALUES(8, 40, 14500.00);
INSERT INTO EMPLOYEE VALUES(9, 40, 44500.00);
INSERT INTO EMPLOYEE VALUES(10, 50, 6500.00);
INSERT INTO EMPLOYEE VALUES(11, 50, 7500.00);
INSERT INTO EMPLOYEE VALUES(12, 50, 8500.00);

 

简单查询

SELECT * FROM EMPLOYEE;

 

分组排序查询

SELECT EMPID, DEPTID, SALARY, 
  ROW_NUMBER() OVER (PARTITION BY DEPTID ORDER BY SALARY DESC) rn
FROM EMPLOYEE;

 

Reference

https://docs.oracle.com/database/121/SQLRF/functions170.htm#SQLRF06100