oracle ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)

工作中遇到的一个问题，需要对某列进行分组排序，取其中排序的第一条数据项

用到了ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)来解决此问题。

实例准备：

CREATE OR REPLACE TABLE EMPLOYEE (

  EMPID INT,

  DEPTID INT,

  SALARY DECIMAL(10,2)

);

INSERT INTO EMPLOYEE VALUES(1,10,234.00);

INSERT INTO EMPLOYEE VALUES(2,10,1233.00);

INSERT INTO EMPLOYEE VALUES(3,20,600.00);

INSERT INTO EMPLOYEE VALUES(4,20,4512.00);

INSERT INTO EMPLOYEE VALUES(5,30,3424.00);

INSERT INTO EMPLOYEE VALUES(6,30,1232.00);

INSERT INTO EMPLOYEE VALUES(7,40,4445.00);

INSERT INTO EMPLOYEE VALUES(8,40,9999.00);

INSERT INTO EMPLOYEE VALUES(9,40,212000.00);

进行查询操作：

SELECT DEPTID

       ,SALARY

       ,ROW_NUMBER() OVER(PARTITION BY DEPTID ORDER BY SALARY DESC) AS RN

  FROM EMPLOYEE;

执行结果：

从上面的结果可以看出来是对部门进行分组，按照薪水来进行排序，之后的处理可以根据自己的需求来进行处理就OK了！

巴特西

oracle ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)

最新文章

热门文章