mysql実装Oracleのrow_number over partition by
882 ワード
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