Recursive SQL

개념 :
SQL 이 좋은 언이이긴 하지만 집합처리 언어이기 때문에 불편한점이 많은 것이 사실이다.
특히 아래와 같은 구조에서 세로를 가로로 바꿀려고 하면 힘들다.

SELECT DEPTNO, ENAME
FROM EMP;

DEPTNO ENAME
------ ----------
10 CLARK
10 KING
10 MILLER
20 SMITH
20 ADAMS
20 FORD
20 SCOTT
20 JONES
30 ALLEN
30 BLAKE
30 MARTIN
30 JAMES
30 TURNER
30 WARD

위의 데이터를 아래처럼 부서번호별로 가로로 바꾸어야 하는것이 미션이다.

DEPTNO ENAME
------- ------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

오라클의 경우 조금 복잡한 Recursive SQL 을 작성해야 한다.

select deptno,
ltrim(sys_connect_by_path(ename,','),',') ename
from (
select deptno,
ename,
row_number() over (partition by deptno order by empno) rn,
count(*) over (partition by deptno) cnt
from emp
)
where level = cnt
start with rn = 1
connect by prior deptno = deptno and prior rn = rn-1 ;

그럼 mySQL은 어떨까?
놀랍게도 mySQL 은 group_concat 한방으로 해결하고 있다.

select deptno,
group_concat(ename order by empno separator, ',') as ename
from emp
group by deptno;

사실 필자는 오라클 9i 이전버젼부터 mySQL 의 정규식을 부러워 했었다.
mySQL 은 정말 그렇고 그런? DBMS 일까? 다시생각해야 될필요가 있다.

그럼 DB2 나 SQL SERVER 는 Recursive SQL 이 사용가능할까?

현재 많은 사람들이 Recursive 쿼리가 안된다고 판단하고 Self Join 을 사용하고 있다.
그러나 DB2나 SQL 서버의 최신버젼은 아래와 같이 Recursive With 를 사용하면 된다.
with x (deptno, cnt, list, empno, len)
as (
select deptno, count(*) over (partition by deptno),
cast(ename as varchar(100)), empno, 1
from emp
union all
select x.deptno, x.cnt, x.list ',' e.ename, e.empno, x.len+1
from emp e, x --> recursive call
where e.deptno = x.deptno
and e.empno > x. empno
)
select deptno,list
from x
where len = cnt ;

참고로 오라클은 Recursive With 절 이나 group_concat 은 11g 버젼에서도 지원하지 않고 있다.
오라클에서는 Recursive function 은 예전부터 지원되어 왔다.

참조서적 : SQL Cookbook By Anthony Molinaro(O'Reilly)