데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

아래같이 절차가 필요할 듯합니다.. 오라클의 scott.emp 테이블로 테스트 해본 내용입니다.

> select ename,sal,deptno from scott.emp;

ENAME SAL DEPTNO

-------------------- ---------- ----------

SMITH 800 20

ALLEN 1600 30

WARD 1250 30

JONES 2975 20

MARTIN 1250 30

BLAKE 2850 30

CLARK 2450 10

SCOTT 3000 20

KING 5000 10

TURNER 1500 30

ADAMS 1100 20

JAMES 950 30

FORD 3000 20

MILLER 1300 10

> select ename, sal, deptno, row_number() over (partition by deptno order by sal) as rn from scott.emp;

ENAME SAL DEPTNO RN

-------------------- ---------- ---------- ----------

MILLER 1300 10 1

CLARK 2450 10 2

KING 5000 10 3

SMITH 800 20 1

ADAMS 1100 20 2

JONES 2975 20 3

SCOTT 3000 20 4

FORD 3000 20 5

JAMES 950 30 1

MARTIN 1250 30 2

WARD 1250 30 3

TURNER 1500 30 4

ALLEN 1600 30 5

BLAKE 2850 30 6

인포믹스도 동일한 데이터로 아래와 같이 해봤습니다.

partition by절의 필드별로 임시테이블을 만들고 sequence를 사용했습니다.

select ename, sal, deptno from emp where deptno=10 order by sal into temp temp1a;

create sequence myseq;

select *, myseq.nextval from temp1a into temp temp1b;

drop sequence myseq;

select ename, sal, deptno from emp where deptno=20 order by sal into temp temp2a;

create sequence myseq;

select *, myseq.nextval from temp2a into temp temp2b;

drop sequence myseq;

select ename, sal, deptno from emp where deptno=30 order by sal into temp temp3a;

create sequence myseq;

select *, myseq.nextval from temp3a into temp temp3b;

drop sequence myseq;

각 temp테이블을 union all로 조회해보았습니다.

select * from temp1b

union all

select * from temp2b

union all

select * from temp3b

ename sal deptno nextval

MILLER 1300 10 1

CLARK 2450 10 2

KING 5000 10 3

SMITH 800 20 1

ADAMS 1100 20 2

JONES 2975 20 3

FORD 3000 20 4

SCOTT 3000 20 5

JAMES 950 30 1

WARD 1250 30 2

MARTIN 1250 30 3

TURNER 1500 30 4

ALLEN 1600 30 5

BLAKE 2850 30 6

좀 무식한 방법이긴 합니다만 참고가 되셨으면 좋겠네요.

정상규(pajama)님이 2015-07-17 22:15에 작성한 댓글입니다.