千呼万唤使出来，MySQL8终于支持分析函数了。

-- auto-generated definition
create table AB_EMPLOYEE
(
  EMP_ID    varchar(15) null,
  EMP_NAME  varchar(25) null,
  DEPT_ID   varchar(15) null,
  EXPERTISE varchar(25) null,
  SALARY    decimal     null,
  RESULTS   varchar(10) null
);

INSERT INTO `AB_EMPLOYEE` VALUES ('5003','ABINASH','1','SCIENCE',50000,'PASS'),('5003','ABINASH','1','ENGLISH',50000,'PASS'),('5003','ABINASH','1','MATH',50000,'PASS'),('107','AMARESH','2','MATH',50000,'PASS'),('107','AMARESH','2','ENGLISH',50000,'PASS'),('105','JYOTI','3','MATH',75000,'FAIL'),('105','JYOTI','3','ENGLISH',75000,'PASS'),('7003','NISHAD','2','ENGLISH',70000,'FAIL'),('7003','NISHAD','2','MATH',70000,'PASS'),('6003','RAKESH','2','MATH',50556,'PASS'),('6003','RAKESH','2','ENGLISH',50556,'FAIL'),('104','RAVI','2','MATH',70000,'PASS'),('104','RAVI','2','ENGLISH',70000,'PASS'),('106','REDDY','2','MATH',80000,'FAIL'),('106','REDDY','2','ENGLISH',80000,'PASS');

select *,
  ROW_NUMBER() OVER(partition by DEPT_ID order by SALARY desc) as ROW_NUM,
  RANK() OVER(partition by DEPT_ID order by SALARY desc) as rank_num,
  DENSE_RANK() over(partition by DEPT_ID order by SALARY desc) as d_rank_num
from AB_EMPLOYEE
ORDER BY DEPT_ID,SALARY DESC

 

转载于:https://my.oschina.net/u/3103453/blog/3042381