MS-SQL 2005 부터 추가된 재귀쿼리를 설명 하고자 한다.
트리구조 쿼리에 사용하면 유용할 거 같아서 포스팅을 하게 되었다.
with xxx(col 1,col 2...col n)
as (
table
union
table
)
select * from xxx order by order column
오라클 재귀쿼리 호출했던 같은 구조의 테이블로 샘플을 짜보겠다.
2014/08/12 - [DB/Oracle] - 오라클 계층형 트리구조 쿼리 (재귀호출) - START WITH .. CONNECT BY PRIOR
1. 테이블 생성
create table tree_table(
id int not null,
parent_id int not null,
name nvarchar(30) not null
);
2. 트리쿼리 출력을 위한 임의의 데이터 생성
-- 1depth insert into tree_table(id,parent_id,name) values(1,0,'1depth 1'); insert into tree_table(id,parent_id,name) values(2,0,'1depth 2'); insert into tree_table(id,parent_id,name) values(3,0,'1depth 3'); -- 2depth 1 insert into tree_table(id,parent_id,name) values(4,1,'2depth 1-1'); insert into tree_table(id,parent_id,name) values(5,1,'2depth 1-2'); insert into tree_table(id,parent_id,name) values(6,1,'2depth 1-3'); -- 2depth 2 insert into tree_table(id,parent_id,name) values(7,2,'2depth 2-1'); insert into tree_table(id,parent_id,name) values(8,2,'2depth 2-2'); insert into tree_table(id,parent_id,name) values(9,2,'2depth 2-3'); -- 3depth 1 insert into tree_table(id,parent_id,name) values(10,4,'3depth 1-1'); insert into tree_table(id,parent_id,name) values(11,4,'3depth 1-2'); insert into tree_table(id,parent_id,name) values(12,4,'3depth 1-3');
3. 재귀쿼리로 트리목록 출력
WITH tree_query AS (
SELECT
id
, parent_id
, name
, convert(varchar(255), id) sort
, convert(varchar(255), name) depth_fullname
FROM tree_table
WHERE parent_id = 0
UNION ALL
SELECT
B.id
, B.parent_id
, B.name
, convert(varchar(255), convert(nvarchar,C.sort) + ' > ' + convert(varchar(255), B.id)) sort
, convert(varchar(255), convert(nvarchar,C.depth_fullname) + ' > ' + convert(varchar(255), B.name)) depth_fullname
FROM tree_table B, tree_query C
WHERE B.parent_id = C.id
)
SELECT id, parent_id, name, depth_fullname FROM tree_query order by SORT
위의 재귀쿼리 실행결과를 확인해 보도록 하자
위와같이 트리구조로 출력되는것을 확인하였다.
구조로 따지자면,
1depth 1
|
-------- 2depth 1-1
| |
| ---- 3depth 1-1
| |
| ---- 3depth 1-2
| |
| ---- 3depth 1-3
-------- 2depth 1-2
|
-------- 2depth 1-3
|
1depth 2
|
-------- 2depth 2-1
|
-------- 2depth 2-2
|
-------- 2depth 2-2
|
1depth 3
와같은 구조로 출력되었다.
정렬이 중요한대 위의 정렬은 데이터가 많지 않아 잘 나오는거같다.
만약 재귀쿼리가 정상적으로 출력이 되지 않으면 sort에 대한 부분은 고민하고 수정해보도록 하자.
마지막으로 잘 활용해서 실무에 적용해보도록 합시다~
출처: https://roqkffhwk.tistory.com/140 [야근싫어하는 개발자]
CREATE TABLE SAMPLE3 (
2 | 0 | ㄴ이순신할아버지 | 1 | 1 | 이순신할아버지 | 1 |
1 | 0 | ㄴ홍길동할아버지 | 2 | 2 | 홍길동할아버지 | 1 |
5 | 1 | ㄴㄴ홍길동아버지2 | 1 | 0002 > 0001 | 홍길동할아버지 > 홍길동아버지2 | 2 |
4 | 1 | ㄴㄴ홍길동아버지1 | 2 | 0002 > 0002 | 홍길동할아버지 > 홍길동아버지1 | 2 |
8 | 4 | ㄴㄴㄴ홍길동아버지1의딸 | 1 | 0002 > 0002 > 0001 | 홍길동할아버지 > 홍길동아버지1 > 홍길동아버지1의딸 | 3 |
7 | 4 | ㄴㄴㄴ홍길동아버지1의아들 | 2 | 0002 > 0002 > 0002 | 홍길동할아버지 > 홍길동아버지1 > 홍길동아버지1의아들 | 3 |
3 | 0 | ㄴ개발로짜할아버지 | 3 | 3 | 개발로짜할아버지 | 1 |
6 | 3 | ㄴㄴ개발로짜아버지 | 1 | 0003 > 0001 | 개발로짜할아버지 > 개발로짜아버지 | 2 |
9 | 6 | ㄴㄴㄴ개발로짜아들 | 1 | 0003 > 0001 > 0001 | 개발로짜할아버지 > 개발로짜아버지 > 개발로짜아들 | 3 |
WITH 절은 CTE(Common Table Expression)를 표현하는 구문이다.
CTE는 기존의 뷰, 파생 테이블, 임시 테이블 등으로 사용되던 것을 대신할 수 있으며,
더 간결한 식으로 보여지는 장점이 있다.
CTE는 ANSI-SQL99 표준에서 나온 것이다. 기존의 SQL은 대부분 ANSI-SQL92가 기준으로 하지만
최근의 DBMS는 대게 ANSI-SQL99와 호환이 되므로 다른 DBMS에서도 SQL Server와 같거나 비슷한 방식으로 응용된다.
CTE는 비재귀적(Non-Recursive) CTE와 재귀적(Recursive) CTE 두 가지가 있다.
(1) 비재귀적 CTE
비재귀적 CTE는 말 그대로 재귀적이지 않은 CTE이다.
단순한 형태이며, 복잡한 쿼리 문장을 단순화시키는 데 적합하게 사용될 수 있다.
비재귀적 CTE 형식
WITH CTE_테이블이름(열이름)
AS
(
<쿼리문>
)
SELECT 열이름 FROM CTE_테이블이름 ;
예시 )
총 구매액이 많은 사용자 순서로 정렬하자
WITH abc(userID, total)
AS
( SELECT userID, SUM(price*amount)
FROM buyTbl GROUP BY userID )
SELECT * FROM abc ORDER BY total DESC ;
CTE는 제일 아래 행의 SELECT 문만 보면 된다.
그리고 제일 아래의 'FROM abc'에서 abc는 실존하는 테이블이 아니라, 바로 위에서 WITH 구문으로 만든
SELECT 결과이다. 단 여기서 'AS (SELECT …'에서 조회하는 열과 'WITH abc (…' 와는 개수가 일치해야 한다.
예시) DB명:sqlDB / 테이블명:userTbl
각 지역별로 큰 키를 1명씩 뽑은 후에, 그 사람들 키의 평균을 구하자.
한꺼번에 생각하지 말고, 한 단계씩 분할하여 생각해 보자.
1단계 → '각 지역별로 가장 큰 키'를 뽑는 쿼리
SELECT addr, MAX(height) FROM userTbl GROUP BY addr
2단계 → 앞의 쿼리를 WITH 구문으로 묶는다.
WITH cte_테이블이름(addr, maxHeight)
AS
(SELECT addr, MAX(height) FROM userTbl GROUP BY addr)
3단계 → 키의 평균을 구하는 쿼리를 작성한다.
SELECT AVG(키) FROM cte_테이블 이름
4단계 → 2단계와 3단계의 쿼리를 합친다. 키의 평균을 실수로 만들려고 키에다 1.0을 곱해서 실수로 변환했다.
WITH cte_userTbl(addr, maxHeight)
AS
(SELECT addr, MAX(height) FROM userTbl GROUP BY addr)
SELECT AVG(maxHeight*1.0) AS [각 지역별 최고키의 평균] FROM cte_userTbl ;
(2) 재귀적 CTE
재귀적이라는 의미는 자기 자신을 반복적으로 호출한다는 의미를 내포한다.
직원 이름(EMP) - 기본키 | 상관 이름(MANAGER) | 부서(DEPARTMENT) |
나사장 | 없음 (NULL) | 없음 (NULL) |
김재무 | 나사장 | 재무부 |
김부장 | 김재무 | 재무부 |
이부장 | 김재무 | 재무부 |
우대리 | 이부장 | 재무부 |
지사원 | 이부장 | 재무부 |
이영업 | 나사장 | 영업부 |
한과장 | 이영업 | 영업부 |
최정보 | 나사장 | 정보부 |
윤차장 | 최정보 | 정보부 |
이주임 | 윤차장 | 정보부 |
이것을 테이블로 만들어 보자.
지금은 데이터가 몇개 없고, 테이블을 봐도 상관과 부하 직원의 관계가 보일 수 있지만, 실무에서 많은
직원들이 앞의 테이블만 가지고는 도저히 누가 누구의 상관인지를 파악하기 어렵다.
특히, 사원번호 등이 코드로 되어 있다면 더욱 그러할 것이다.
재귀적 CTE의 기본 형식은 다음과 같다.
WITH CTE_테이블이름(열이름)
AS
(
<쿼리문1 : SELECT * FROM 테이블A >
UNION ALL
<쿼리문2 : SELECT * FROM 테이블A JOIN CTE_테이블이름>
)
SELECT * FROM CTE_테이블이름 ;
위의 구문에서 <쿼리문1>을 앵커 멤버(Anchor Member:AM)이라고 부르고,
<쿼리문2>를 재귀멤버(Recursive Member:RM)라고 지칭한다.
① <쿼리문1>을 실행한다. 이것이 루틴의 최초 호출에 해당한다. 그리고 기본값은 0으로 초기화된다.
② <쿼리문2>를 실행한다. 기본값을 기본값+1로 증가시킨다. 그런데 SELECT의 결과가 빈것이 아니라면,
'CTE_테이블이름'을 다시 재귀적으로 호출한다
③ 계속 ②번을 반복한다. 단 SELECT의 결과가 아무것도 없다면 재귀적인 호출이 중단된다.
④ 외부의 SELECT 문을 실행해서 앞 단계에서의 누적된 결과(UNION ALL)를 가져온다.
0. 우선 앞의 테이블을 정의하고 데이터를 입력하자
0-1 테이블을 정의해 보자
CREATE TABLE empTbl (emp NCHAR(3), manager NCHAR(3), department NCHAR(3)) ;
GO
0-2 표와 같은 데이터를 입력해 보자
INSERT INTO empTbl VALUES ('나사장',NULL,NULL);
INSERT INTO empTbl VALUES ('김재무','나사장','재무부');
INSERT INTO empTbl VALUES ('김부장','김재무','재무부');
INSERT INTO empTbl VALUES ('이부장','김재무','재무부');
INSERT INTO empTbl VALUES ('우대리','이부장','재무부');
INSERT INTO empTbl VALUES ('지사원','이부장','재무부');
INSERT INTO empTbl VALUES ('이영업','나사장','영업부');
INSERT INTO empTbl VALUES ('한과장','이영업','영업부');
INSERT INTO empTbl VALUES ('최정보','나사장','정보부');
INSERT INTO empTbl VALUES ('윤차장','최정보','정보부');
INSERT INTO empTbl VALUES ('이주임','윤차장','정보부');
1. 재귀적 CTE의 구문 형식에 맞춰서 쿼리문을 만들어 보자.
아직 조인(JOIN)을 배우지 않아서 <쿼리문2> 부분이 좀 어렵겠지만, 형식대로 empTbl과 empCTE를 조인하는 방식이다.
WITH empCTE(empName, mgrName, dept, level)
AS
(
SELECT emp, manager, department , 0
FROM empTbl
WHERE manager IS NULL -- 상관이 없는 사람이 바로 사장
UNION ALL
SELECT AA.emp, AA.manager, AA.department, BB.level+1
FROM empTbl AS AA INNER JOIN empCTE AS BB
ON AA.manager = BB.empName
)
SELECT * FROM empCTE ORDER BY dept, level ;
AA, BB는 각 테이블의 별칭을 붙여준 것이다. 즉, AA.emp는 empTbl.emp(empTbl 테이블의 emp열)과 같은 의미이다.
2. 쿼리문을 약간 수정해서 조금 더 보기쉽게 만들어 보자
WITH empCTE(empName, mgrName, dept, level)
AS
(
SELECT emp, manager, department , 0
FROM empTbl
WHERE manager IS NULL -- 사장
UNION ALL
SELECT AA.emp, AA.manager, AA.department, BB.level+1
FROM empTbl AS AA INNER JOIN empCTE AS BB
ON AA.manager = BB.empName
)
SELECT replicate(' ㄴ',level) + empName AS [직원이름], dept [직원부서]
FROM empCTE ORDER BY dept, level
replicate(문자, 개수) 함수는 해당 문자를 개수만큼 반복하는 함수이다.
그러므로 레벨에 따라서 'ㄴ' 문자를 출력함으로써 트리 구조 형태로 보여지는 효과를 줬다.
3. 이번에는 사원급을 제외한 부장/차장/과장급까지만 출력해 보자. 레벨 2이므로 간단히
'WHERE level <2' 만 <쿼리문2> 부분에 추가해 주면 된다.
WITH empCTE(empName, mgrName, dept, level)
AS
(
SELECT emp, manager, department , 0
FROM empTbl
WHERE manager IS NULL -- 사장
UNION ALL
SELECT AA.emp, AA.manager, AA.department, BB.level+1
FROM empTbl AS AA INNER JOIN empCTE AS BB
ON AA.manager = BB.empName
WHERE level < 2
)
SELECT replicate(' ㄴ',level) + empName AS [직원이름], dept [직원부서]
FROM empCTE ORDER BY dept, level
오라클 SQL 문
select *
from TEST.MENU A
START WITH A.UP_MENU_NO = '1'
CONNECT BY PRIOR A.MENU_NO = A.UP_MENU_NO
ORDER SIBLINGS BY A.SORT_NO
=========================================================================>
위의 오라클 sql 문을 ms-sql 문으로 변환
MS-SQL 문
;WITH CTE (UP_MENU_NO, MENU_NO, MENU_LEVEL , MENU_NAME, SORT ) AS (
SELECT
UP_MENU_NO AS UP_MENU_NO
, '0' AS MENU_NO
, MENU_LEVEL
, MENU_NAME
, convert(varchar(255), right(N'00'+rtrim(SORT_NO),3) ) AS SORT // 1을 001 형태의 3자리 로 바꾸준다.
FROM MENU
WHERE UP_MENU_NO = '1'
UNION ALL
SELECT
B.UP_MENU_NO AS UP_MENU_NO
, B.MENU_NO AS MENU_NO
, C.MENU_LEVEL + 1 AS MENU_LEVEL
, B.MENU_NAME
, convert(varchar(255), convert(nvarchar,C.SORT) + '|' + right(N'00'+rtrim(B.SORT_NO),3) ) AS SORT
FROM MENU B
INNER JOIN CTE C ON B.UP_MENU_NO = C.MENU_NO
)
SELECT * FROM CTE order by SORT
출처: https://d4emon.tistory.com/30 [게으른 개발자!!!]
출처: https://d4emon.tistory.com/30 [게으른 개발자!!!]
WITH SYS_MENU_TREE(ID_MENU, ID_MENU_UPPER, ID_PGM, NM_MENU_KO, NM_MENU_EN, NO_LEVEL, NO_ORDER, LVL, SORT) AS
(
SELECT ID_MENU AS ID_MENU /* 메뉴ID */
, ID_MENU_UPPER AS ID_MENU_UPPER /* 상위메뉴ID */
, ID_PGM AS ID_PGM /* 프로그램ID */
, NM_MENU_KO AS NM_MENU_KO /* 메뉴명_한글 */
, NM_MENU_EN AS NM_MENU_EN /* 메뉴명_영문 */
, NO_LEVEL AS NO_LEVEL /* 메뉴레벨 */
, NO_ORDER AS NO_ORDER /* 메뉴정렬순서 */
, 1 AS LVL /* 트리레벨 */
, CONVERT(VARCHAR,NO_ORDER) AS SORT /* 트리정렬순서 */
FROM SYS_MENU /* 시스템공통_메뉴 */
WHERE ID_MENU_UPPER = #{idMenu}
AND YN_USE = #{ynUse}
UNION ALL
SELECT SM.ID_MENU AS ID_MENU /* 메뉴ID */
, SM.ID_MENU_UPPER AS ID_MENU_UPPER /* 상위메뉴ID */
, SM.ID_PGM AS ID_PGM /* 프로그램ID */
, SM.NM_MENU_KO AS NM_MENU_KO /* 메뉴명_한글 */
, SM.NM_MENU_EN AS NM_MENU_EN /* 메뉴명_영문 */
, SM.NO_LEVEL AS NO_LEVEL /* 메뉴레벨 */
, SM.NO_ORDER AS NO_ORDER /* 메뉴정렬순서 */
, SMT.LVL + 1 AS LVL /* 트리레벨 */
, CONVERT(VARCHAR,CONVERT(VARCHAR,SMT.NO_ORDER)+CONVERT(VARCHAR,SM.NO_ORDER)) AS SORT /* 트리정렬순서 */
FROM SYS_MENU SM /* 시스템공통_메뉴 */
, SYS_MENU_TREE SMT /* 시스템공통_메뉴트리 */
WHERE SM.ID_MENU_UPPER = SMT.ID_MENU
AND SM.YN_USE = #{ynUse}
)
SELECT ID_MENU /* 메뉴ID */
, ID_MENU_UPPER /* 상위메뉴ID */
, ID_PGM /* 프로그램ID */
, NM_MENU_KO /* 메뉴명_한글 */
, NM_MENU_EN /* 메뉴명_영문 */
, NO_LEVEL /* 메뉴레벨 */
, NO_ORDER /* 메뉴정렬순서 */
, LVL /* 트리레벨 */
, SORT /* 트리정렬순서 */
FROM SYS_MENU_TREE
ORDER BY SORT