CTE(공통 테이블 식) (MS-SQL)

ms-sql에는 재귀함수와 같은 역할을 하는 문법이 존재한다.

이는 단순 쿼리에서 파생되며 단일 select, insert, update, delete 또는 merge문 실행 범위 내에서 정의됨

CTE는 비재귀적 CTE와 재귀적 CTE로 구분 할 수 있습니다.

 

재귀적이라는 의미는 자기 자신을 반복적으로 호출하는 것을 의미합니다. (BOM, 부서계층그룹에 해당)

비재귀적은 단순한 형태의 쿼리를 얘기하며, 복잡한 쿼리 문장을 단순화 시키는데 적합하게 사용될 수 있습니다.

 

기본 문법

;WITH CTE_테이블이름(열이름)
AS
(
    <SELECT 쿼리문>
)
SELECT 열이름 FROM CTE_테이블이름;

WITH 키워드 다음에는 CTE의 이름을 지정하고 그 다음에는 선택적인 열 별칭 목록을 지정합니다.

열 별칭은 CTE 내의 select문에서 반환하는 열에 해당합니다.

선택적인 열 별칭 다음에 필수 항목인 AS 키워드를 지정합니다.

그리고 AS 키워드 다음에는 CTE를 정의하는 쿼리식을 괄호로 감싸고 지정합니다.

 

예시

USE ACCT_AC;
;WITH CTE_SAMPLE(GYE_CODE,GYE_DES)
AS
(
    SELECT GYE_CODE,GYE_DES FROM ACC002
)
SELECT * FROM CTE_SAMPLE;

재귀적 예제

기본 데이터 생성

CREATE TABLE SAMPLE_CTE1
(
  GRP VARCHAR(4) NOT NULL PRIMARY KEY,
  GRP_DES NVARCHAR(10),
  PARENT_GRP VARCHAR(4)
);

INSERT INTO SAMPLE_CTE1(GRP,GRP_DES,PARENT_GRP) VALUES
('01','한국',NULL),('02','서울본사','01'),('03','개발본부','02'),('04','개발팀','03');

기본 문법

;WITH CTE_테이블이름(열이름)
AS(
    <쿼리문1: SELECT * FROM 테이블A>
    UNION ALL
    <쿼리문2: SELECT * FROM 테이블B JOIN CTE_테이블이름>
)
SELECT * FROM CTE_테이블이름;

위의 구문에서 <쿼리문1>을 앵커 멤버<Anchor Member)라고 부르고, <쿼리문2>를 재귀멤버<Recursive Member>라고 칭한다.

 

1. <쿼리문1>을 실행한다. 이것이 루틴의 최초 호출에 해당한다. 그리고, 기본값은 0으로 초기화된다.

2. <쿼리문2>를 실행한다. 기본값을 기본값+1로 증가시킨다. 그런데 select의 결과가 빈값이 아니라면, 'CTE_테이블이름'을 다시 재귀적으로 호출한다.

3. 계속 2번을 반복한다. select의 결과가 아무것도 없다면 재귀적인 호출이 중단된다.

4. 외부의 select문을 실행해서 앞 단계에서의 누적된 결과(union all)를 가져온다.

 

쿼리구현

;WITH CTE(GRP,GRP_DES,PARENT_GRP,LVL)
AS
(
       -- 찾고자하는기준이되는그룹정보를조회
       SELECT GRP,GRP_DES,PARENT_GRP,0 AS LVL
       FROM SAMPLE_GROUP1
       WHERE GRP = '01'
       UNION ALL
       -- 위에서찾은기준을가지고그룹의상위정보를가지고옴
       SELECT A.GRP,A.GRP_DES,A.PARENT_GRP,LVL+1
       FROM SAMPLE_GROUP1 A
              INNER JOIN CTE B
                     ON A.PARENT_GRP = B.GRP
)
SELECT GRP,GRP_DES,PARENT_GRP,LVL FROM CTE
ORDER BY LVL;