sumover mysql_mysql之 sum() over(partition by)

SELECT empno,

ename,

sal,

IF(@deptno = deptno, @sal := @sal, @sal := s_sal) AS s_sal,

@deptno := deptno

FROM

(

SELECT empno,

ename,

sal,

IF(@deptno = deptno, @sal := @sal + sal, @sal := sal) AS s_sal,

@deptno := deptno AS deptno

FROM (SELECT @sal := 0, @deptno := 0) a0, emp t

ORDER BY deptno, empno

)e

ORDER BY deptno, empno DESC;

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

| empno | ename | sal | s_sal | @deptno := deptno |

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

| 7934 | MILLER | 1300 | 8750 | 10 |

| 7839 | KING | 5000 | 8750 | 10 |

| 7782 | CLARK | 2450 | 8750 | 10 |

| 7902 | FORD | 3000 | 10875 | 20 |

| 7876 | ADAMS | 1100 | 10875 | 20 |

| 7788 | SCOTT | 3000 | 10875 | 20 |

| 7566 | JONES | 2975 | 10875 | 20 |

| 7369 | SMITH | 800 | 10875 | 20 |

| 7900 | JAMES | 950 | 9400 | 30 |

| 7844 | TURNER | 1500 | 9400 | 30 |

| 7698 | BLAKE | 2850 | 9400 | 30 |

| 7654 | MARTIN | 1250 | 9400 | 30 |

| 7521 | WARD | 1250 | 9400 | 30 |

| 7499 | ALLEN | 1600 | 9400 | 30 |

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

14 rows in set (0.01 sec)