mysq 之 dense_rank() over()

SET @rn=0;
SET @last_deptno=-1;
SET @last_sal=-1;
SELECT deptno,
       empno,
       ename,
       sal,
       IF(@last_deptno = deptno, @rn := @rn + IF(@last_sal = sal,0,1), @rn := 1) AS dense_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  | dense_rank | last_sal | last_deptno |
+--------+-------+--------+------+------------+----------+-------------+
|     10 |  7934 | MILLER | 1300 |          1 |       -1 |          10 |
|     10 |  7782 | CLARK  | 2450 |          2 |     2450 |          10 |
|     10 |  7839 | KING   | 5000 |          3 |     5000 |          10 |

|     20 |  7369 | SMITH  |  800 |          1 |       -1 |          20 |
|     20 |  7876 | ADAMS  | 1100 |          2 |     1100 |          20 |
|     20 |  7566 | JONES  | 2975 |          3 |     2975 |          20 |
|     20 |  7788 | SCOTT  | 3000 |          4 |     3000 |          20 |
|     20 |  7902 | FORD   | 3000 |          4 |     3000 |          20 |

|     30 |  7900 | JAMES  |  950 |          1 |       -1 |          30 |
|     30 |  7654 | MARTIN | 1250 |          2 |     1250 |          30 |
|     30 |  7521 | WARD   | 1250 |          2 |     1250 |          30 |
|     30 |  7844 | TURNER | 1500 |          3 |     1500 |          30 |
|     30 |  7499 | ALLEN  | 1600 |          4 |     1600 |          30 |
|     30 |  7698 | BLAKE  | 2850 |          5 |     2850 |          30 |
+--------+-------+--------+------+------------+----------+-------------+
14 rows in set (0.00 sec)



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值