Recursive CTE를 이용한 계층쿼리

Recursive CTE를 이용한 계층쿼리 구현 테스트

*MySQL8.0 부터 구현 가능

​

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

-- SAMPLE DATA

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

SHOW COLLATION WHERE charset = 'euckr';

-- euckr_bin은 문자를 코드화 하여 비교한다.

-- 'A'와 'a'가 다르다

-- euckr_korean_ci은 문자와 문자를 비교 --> 비교대상(필터링) 많아짐

-- 'A'와 'a'가 같다

​

DROP DATABASE IF EXISTS scott;

CREATE DATABASE IF NOT EXISTS scott;

USE scott;

​

​

DROP TABLE IF EXISTS EMP;

DROP TABLE IF EXISTS DEPT;

DROP TABLE IF EXISTS SALGRADE;

​

CREATE TABLE DEPT (

DEPTNO INT(10) NOT NULL,

DNAME VARCHAR(14) NOT NULL,

LOC VARCHAR(13) NOT NULL,

PRIMARY KEY (DEPTNO)

) ENGINE=InnoDB DEFAULT CHARSET=euckr COLLATE=euckr_bin;

​

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', '뉴욕');

INSERT INTO DEPT VALUES (20, 'RESEARCH', '달라스');

INSERT INTO DEPT VALUES (30, 'SALES', '시카고');

INSERT INTO DEPT VALUES (40, 'OPERATIONS', '보스턴');

​

SELECT * FROM DEPT;

​

CREATE TABLE EMP (

EMPNO INT(4) NOT NULL,

ENAME VARCHAR(10) NOT NULL,

JOB VARCHAR(9) NOT NULL,

MGR INT(4) ,

HIREDATE DATE NOT NULL,

SAL DECIMAL(7,2) NOT NULL,

COMM DECIMAL(7,2) ,

DEPTNO INT(10) NOT NULL,

PRIMARY KEY (EMPNO)

) ENGINE=InnoDB DEFAULT CHARSET=euckr COLLATE=euckr_bin;

​

INSERT INTO EMP VALUES (7839,'김가훈' ,'사장' ,NULL,'1981-11-17',5000,NULL,10);

INSERT INTO EMP VALUES (7698,'박지영' ,'매니저' ,7839,'1981-05-01',2850,NULL,30);

INSERT INTO EMP VALUES (7782,'박재연' ,'매니저' ,7839,'1981-05-09',2450,NULL,10);

INSERT INTO EMP VALUES (7566,'김가연' ,'매니저' ,7839,'1981-04-01',2975,NULL,20);

INSERT INTO EMP VALUES (7654,'홍하연' ,'판매자' ,7698,'1981-09-10',1250,1400,30);

INSERT INTO EMP VALUES (7499,'신지영' ,'판매자' ,7698,'1981-02-11',1600,300 ,30);

INSERT INTO EMP VALUES (7844,'신민영' ,'판매자' ,7698,'1981-08-21',1500,0 ,30);

INSERT INTO EMP VALUES (7900,'최성군' ,'점원' ,7698,'1981-12-11',950 ,NULL,30);

INSERT INTO EMP VALUES (7521,'하동길' ,'판매자' ,7698,'1981-02-23',1250,500 ,30);

INSERT INTO EMP VALUES (7902,'염지훈' ,'분석가' ,7566,'1981-12-11',3000,NULL,20);

INSERT INTO EMP VALUES (7369,'서동진' ,'점원' ,7902,'1980-12-09',800 ,NULL,20);

INSERT INTO EMP VALUES (7788,'노길준' ,'분석가' ,7566,'1982-12-22',3000,NULL,20);

INSERT INTO EMP VALUES (7876,'김성하' ,'점원' ,7788,'1983-01-15',1100,NULL,20);

INSERT INTO EMP VALUES (7934,'김지혜' ,'점원' ,7782,'1982-01-11',1300,NULL,10);

SELECT * FROM EMP;

​

​

CREATE TABLE SALGRADE

( GRADE INT(10) NOT NULL,

LOSAL INT(10) NOT NULL,

HISAL INT(10) NOT NULL,

PRIMARY KEY (GRADE)

) ENGINE=InnoDB DEFAULT CHARSET=euckr COLLATE=euckr_bin;

​

INSERT INTO SALGRADE VALUES(1,700,1200);

INSERT INTO SALGRADE VALUES(2,1201,1400);

INSERT INTO SALGRADE VALUES(3,1401,2000);

INSERT INTO SALGRADE VALUES(4,2001,3000);

INSERT INTO SALGRADE VALUES(5,3001,9999);

​

SELECT * FROM SALGRADE;

​

COMMIT;

​

​

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

-- CONNECT BY 예제

-- MySQL 8.0부터 Recursive CTE 사용 가능

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

-- 재귀서브팩토링(Recursive subquery factoring) 기능을 사용 한다.

-- CTE(Common Table Expression) 구문 이라고도 한다.

​

SELECT VERSION();

-- 참고사이트 : https://dev.mysql.com/doc/refman/8.0/en/with.html

-- WITH절 다음 RECURSIVE구문을 사용한다

-- UNION ALL 기준 상위 쿼리 : CTE에 초기값 설정

-- UNION ALL 기준 아래 쿼리 : CTE를 이용해 RECURCIVE 구문에 의해 반복적으로 실행하여 결과값 CTE에 저장

WITH RECURSIVE CTE (n) AS

(

SELECT 1 -- Anchor member

UNION ALL

SELECT n + 1 FROM CTE -- Recursive member

WHERE n < 5 -- Termination member

)

SELECT * FROM CTE; -- Query that users CTE

​

​

-- EMP 테이블의 계층관계

-- 자릿수 설정 안할시 에러 발생 : SQL Error[1406][22001] : Data truncation : Data too long for column 'PRN_ENAME' at row 1

-- PRN_ENAME : CAST로 CHAR(10) 로 자릿수 설정

-- PT : CAST로 CHAR(30) 로 자릿수 설정

WITH RECURSIVE CTE (EMPNO, ENAME, JOB, MGR, LV ,PRN_ENAME, PT)

AS (

SELECT EMPNO, ENAME, JOB, MGR

,1 AS LV -- LEVEL

,CAST('' AS CHAR(10)) AS PRN_ENAME -- PRIOR

,CAST(EMPNO AS CHAR(30)) AS PT -- SYS_CONNECT_BY_PATH

FROM EMP

WHERE MGR IS NULL

UNION ALL

SELECT C.EMPNO, C.ENAME, C.JOB, C.MGR

,P.LV + 1 AS LV -- LEVEL

,P.ENAME AS PRN_ENAME -- PRIOR

,CONCAT_WS('>',P.PT ,C.EMPNO) AS PT -- SYS_CONNECT_BY_PATH

FROM CTE P

INNER JOIN EMP C

ON P.EMPNO = C.MGR

)

SELECT LV, EMPNO, ENAME, JOB, MGR, PRN_ENAME, PT

FROM CTE

ORDER BY PT;

​