mysql实现rank() over_mysql 之 rank() over()

SET @rn=0;

SET @dense_rank=0;

SET @last_deptno=-1;

SET @last_sal=-1;

SET @ADD = 0;

SELECT deptno,

empno,

ename,

sal,

IF(@last_deptno = deptno, @rn := @rn + 1, @rn := 1) AS rn,

IF(@last_sal = sal,@dense_rank := @dense_rank,@dense_rank := @rn) AS rank,

IF(@last_deptno = deptno, @last_sal := sal, @last_sal := -1) AS last_sal,

@last_deptno := deptno AS last_deptno

FROM EMP

ORDER BY deptno, sal;

+--------+-------+--------+------+------+------------+----------+-------------+

| deptno | empno | ename | sal | rn | dense_rank | last_sal | last_deptno |

+--------+-------+--------+------+------+------------+--------