mysql 伪列，实现分组排序,类似oracle的over开窗rank() over()

测试数据 （版本：mysql 5.7）

/* for mysql
drop table emp;
CREATE TABLE emp (EMPNO int NOT NULL primary key,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR int,
HIREDATE varchar(19),
SAL int,
COMM int,
DEPTNO int
);
create index ind_empdno on emp(deptno);
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,     '17-12-1980',800,NULL,50);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,  '20-2-1981',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,   '22-2-1981',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,   '2-4-1981',2975,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,  '17-11-1981',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698, '8-9-1981',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,     '13-7-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,     '3-12-1981',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,    '3-12-1981',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,    '23-1-1982',1300,NULL,10);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698, '28-9-1981',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,   '1-5-1981',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,   '9-6-1981',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,   '13-7-87','dd-mm-rr')-85,3000,NULL,20);
commit;
*/

按empno排列,取行号

select empno,ename,job,@num:=@num+1 rownum from emp e,(select @num:=0) t
order by empno;

等效的另一种实现方式:在select之外定义变量：
set @num:=0;
select empno,ename,job,@num:=@num+1 rownum from emp e t
order by empno;

单个变量只能实现整体排序
+-------+--------+-----------+--------+
| empno | ename  | job       | rownum |
+-------+--------+-----------+--------+
|  7369 | SMITH  | CLERK     |      1 |
|  7499 | ALLEN  | SALESMAN  |      2 |
|  7521 | WARD   | SALESMAN  |      3 |
|  7566 | JONES  | MANAGER   |      4 |
|  7654 | MARTIN | SALESMAN  |      5 |
|  7698 | BLAKE  | MANAGER   |      6 |
|  7782 | CLARK  | MANAGER   |      7 |
|  7839 | KING   | PRESIDENT |      8 |
|  7844 | TURNER | SALESMAN  |      9 |
|  7900 | JAMES  | CLERK     |     10 |
|  7902 | FORD   | ANALYST   |     11 |
|  7934 | MILLER | CLERK     |     12 |
+-------+--------+-----------+--------+

根据job分组，按empno排列,取行号

# 一定要按照分组键，排列键排好顺序 类似于实现Oracle的over()开窗函数
# 要实现分组排序怎么办？需要使用2个变量，当分组值出现变化，计数值重置


select empno,job
,CASE WHEN @pid= e.job THEN @ord := @ord+ 1 
      WHEN @pid := e.job THEN @ord := 0 ELSE @ord := 0 END ord
from emp e,(SELECT @pid := '', @ord := 0) t
order by e.job,empno;
# 注意1 第一个when @pid是“=”号 表示判断是否还是当前分组
# 注意2 第二个when @pid是“:=”号 表示前一个条件不满足，对@pid变量重新赋值
# 注意3 case when不会走到else，所以else @ord:=1 可以不写
# 注意4 ord:=0 在SQL在有3个地方重置:=0 重置的值必定应该是相同的

+-------+-----------+------+
| empno | job       | ord  |
+-------+-----------+------+
|  7902 | ANALYST   |    1 |
|  7369 | CLERK     |    1 |
|  7900 | CLERK     |    2 |
|  7934 | CLERK     |    3 |
|  7566 | MANAGER   |    1 |
|  7698 | MANAGER   |    2 |
|  7782 | MANAGER   |    3 |
|  7839 | PRESIDENT |    1 |
|  7499 | SALESMAN  |    1 |
|  7521 | SALESMAN  |    2 |
|  7654 | SALESMAN  |    3 |
|  7844 | SALESMAN  |    4 |
+-------+-----------+------+

如果groupby的是数值类型部门编号：DEPTNO

select empno,deptno
,CASE WHEN @pid= e.DEPTNO THEN @ord := @ord+ 1 WHEN @pid := e.DEPTNO THEN @ord := 1 ELSE @ord := 1 END ord
from emp e,(SELECT @pid := -1, @ord := 1) t
order by deptno,empno;

+-------+--------+------+
| empno | deptno | ord  |
+-------+--------+------+
|  7782 |     10 |    1 |
|  7839 |     10 |    2 |
|  7934 |     10 |    3 |
|  7566 |     20 |    1 |
|  7902 |     20 |    2 |
|  7499 |     30 |    1 |
|  7521 |     30 |    2 |
|  7654 |     30 |    3 |
|  7698 |     30 |    4 |
|  7844 |     30 |    5 |
|  7900 |     30 |    6 |
|  7369 |     50 |    1 |
+-------+--------+------+

根据job,实现工资sal累计求和

select empno,job,sal
,CASE WHEN @pid= e.job THEN @ord := @ord+ e.sal WHEN @pid := e.job THEN @ord := e.sal ELSE @ord := e.sal END ord
from emp e,(SELECT @pid := '', @ord := 0) t
order by job,empno;

+-------+-----------+------+------+
| empno | job       | sal  | ord  |
+-------+-----------+------+------+
|  7902 | ANALYST   | 3000 | 3000 |
|  7369 | CLERK     |  800 |  800 |
|  7900 | CLERK     |  950 | 1750 |
|  7934 | CLERK     | 1300 | 3050 |
|  7566 | MANAGER   | 2975 | 2975 |
|  7698 | MANAGER   | 2850 | 5825 |
|  7782 | MANAGER   | 2450 | 8275 |
|  7839 | PRESIDENT | 5000 | 5000 |
|  7499 | SALESMAN  | 1600 | 1600 |
|  7521 | SALESMAN  | 1250 | 2850 |
|  7654 | SALESMAN  | 1250 | 4100 |
|  7844 | SALESMAN  | 1500 | 5600 |
+-------+-----------+------+------+

根据job,实现上一行数据的sal

select empno,job,sal,ord,ord-sal sal_agg from (
select empno,job,sal
,CASE WHEN @pid= e.job THEN @ord := @ord+ e.sal WHEN @pid := e.job THEN @ord := e.sal ELSE @ord := e.sal END ord
from emp e,(SELECT @pid := '', @ord := 0) t
order by job,empno
) x;

+-------+-----------+------+------+---------+
| empno | job       | sal  | ord  | sal_agg |
+-------+-----------+------+------+---------+
|  7902 | ANALYST   | 3000 | 3000 |       0 |
|  7369 | CLERK     |  800 |  800 |       0 |
|  7900 | CLERK     |  950 | 1750 |     800 |
|  7934 | CLERK     | 1300 | 3050 |    1750 |
|  7566 | MANAGER   | 2975 | 2975 |       0 |
|  7698 | MANAGER   | 2850 | 5825 |    2975 |
|  7782 | MANAGER   | 2450 | 8275 |    5825 |
|  7839 | PRESIDENT | 5000 | 5000 |       0 |
|  7499 | SALESMAN  | 1600 | 1600 |       0 |
|  7521 | SALESMAN  | 1250 | 2850 |    1600 |
|  7654 | SALESMAN  | 1250 | 4100 |    2850 |
|  7844 | SALESMAN  | 1500 | 5600 |    4100 |
+-------+-----------+------+------+---------+