mysql实现Oracle中row_number over partition by

SELECT t1.empno,t1.sal,t1.deptno,
(SELECT COUNT( t2.sal)+1 FROM emp t2 WHERE t1.deptno=t2.deptno AND t1.sal > t2.sal) rn
FROM emp t1
ORDER BY t1.deptno,rn;

EMPNO        SAL        DEPTNO        RN
7934        1300.00        10        1
7782        2450.00        10        2
7839        5000.00        10        3
7369        800.00        20        1
7566        2975.00        20        2
7902        3000.00        20        3
7900        950.00        30        1
7654        1250.00        30        2
7521        1250.00        30        2
7846        1500.00        30        4
7844        1500.00        30        4
7845        1500.00        30        4
7499        1600.00        30        7
7698        2850.00        30        8