TIPS(2) : 특정 조건의 순서대로 번호를 부여하는 방법
=================================================

[ Q1 ] 아래의 Record를 Deptno 순으로 Sequence를 부여하려면?

ENAME JOB DEPTNO
-------- --------- -----
ALLEN SALESMAN 20
WARD SALESMAN 30
JONES MANAGER 20
MARTINN SALESMAN 30
BLAKE MANAGER 30
CLARK MANAGER 10
SCOTT ANALYST 20
KING PRESIDENT 10
TURNER SALESMAN 30
ADAMS CLERK 20
JAMES CLERK 30
FORD ANALYST 20
MILLER CLERK 10

[ A1 ]

1. Emp Table의 Deptno Column에 Index를 생성한다.
create index i_emp on emp(deptno);
2. Table에 Sequence Number Column을 추가한다.
alter table emp add (seq1 number(5));
3. update emp
set seq1 = rownum
where deptno > '0';

ENAME JOB DEPTNO SEQ1
-------- ------------ ------- ------
CLARK MANAGER 10 1
KING PRESIDENT 10 2
MILLER CLERK 10 3
ALLEN SALESMAN 20 4
JONES MANAGER 20 5
SCOTT ANALYST 20 6
ADAMS CLERK 20 7
FORD ANALYST 20 8
WARD SALESMAN 30 9
MARTINN SALESMAN 30 10
BLAKE MANAGER 30 11
TURNER SALESMAN 30 12
JAMES CLERK 30 13



[ Q2 ] 각 부서별로 Sequence를 부여하려면?

[ A2 ]
update emp A
set A.seq1 = (select A.seq1 - min(B.seq1)+1
from emp B
where B.deptno = A.deptno);

ENAME JOB DEPTNO SEQ1
--------- ------------ ------ ----
CLARK MANAGER 10 1
KING PRESIDENT 10 2
MILLER CLERK 10 3
ALLEN SALESMAN 20 1
JONES MANAGER 20 2
SCOTT ANALYST 20 3
ADAMS CLERK 20 4
FORD ANALYST 20 5
WARD SALESMAN 30 1
MARTINN SALESMAN 30 2
BLAKE MANAGER 30 3
TURNER SALESMAN 30 4
JAMES CLERK 30 5