ORACLE&MYSQL同樣的記錄值進行編號處理

ORACLE&MYSQL 同樣資料進行編號處理

oralce10g&mysql5.5

[@more@]

ORACLE:

SQL> select deptno,
2 empno,ename,
3 row_number() over(partition by deptno order by deptno) com_num
4 from emp
5 ;

DEPTNO EMPNO ENAME COM_NUM
---------- ---------- ---------- ----------
10 7782 CLARK 1
10 7934 MILLER 2
10 7839 KING 3
20 7566 JONES 1
20 7902 FORD 2
20 7369 SMITH 3
20 7788 SCOTT 4
30 7499 ALLEN 1
30 7844 TURNER 2
30 7900 JAMES 3
30 7521 WARD 4

DEPTNO EMPNO ENAME COM_NUM
---------- ---------- ---------- ----------
30 7698 BLAKE 5
30 7654 MARTIN 6

已選擇13行。

SQL>

MYSQL:

mysql> select heyf_tmp.deptno,
-> heyf_tmp.empno,
-> heyf_tmp.ename,
-> @rownum := @rownum + 1,
-> if(@deptno = heyf_tmp.deptno, @groupcounts := @groupcounts + 1, @groupcounts := 1) as rank,
-> @deptno := heyf_tmp.deptno
-> from (select deptno, empno, ename from EMP order by deptno, empno) heyf_tmp,
-> (select @rownum := 0, @deptno := null, @groupcounts := 0) a;
+--------+-------+--------+------------------------+------+----------------------------+
| deptno | empno | ename | @rownum := @rownum + 1 | rank | @deptno := heyf_tmp.deptno |
+--------+-------+--------+------------------------+------+----------------------------+
| 10 | 7782 | CLARK | 1 | 1 | 10 |
| 10 | 7839 | KING | 2 | 2 | 10 |
| 10 | 7934 | MILLER | 3 | 3 | 10 |
| 20 | 7369 | SMITH | 4 | 1 | 20 |
| 20 | 7566 | JONES | 5 | 2 | 20 |
| 20 | 7788 | SCOTT | 6 | 3 | 20 |
| 20 | 7902 | FORD | 7 | 4 | 20 |
| 30 | 7499 | ALLEN | 8 | 1 | 30 |
| 30 | 7521 | WARD | 9 | 2 | 30 |
| 30 | 7654 | MARTIN | 10 | 3 | 30 |
| 30 | 7698 | BLAKE | 11 | 4 | 30 |
| 30 | 7844 | TURNER | 12 | 5 | 30 |
| 30 | 7900 | JAMES | 13 | 6 | 30 |
+--------+-------+--------+------------------------+------+----------------------------+
13 rows in set (0.00 sec)