[MySQL] With Recursive 문, 세션 변수 활용, Pagination

1. 기본 테이블 형태

select * from employee;

2. Employee 테이블에서 직원 번호, 직원 이름과 직원의 상사 번호와 상사 이름을 출력

* With 절을 사용하지 않은 쿼리

select b.empno, b.ename, b.mgr, a.ename from employee a right outer join employee b on a.empno = b.mgr;

* With 절(임시 테이블)

with t1 as (select empno, ename, mgr from employee), 
	t2 as (select empno, ename from employee) 
select t1.empno, t1.ename, t1.mgr, t2.ename 
from t1 left outer join t2
on t1.mgr = t2.empno;

쿼리 실행 결과

- t1의 경우

t1

- t2의 경우

t2

t1과 t2를 조인하여 결과를 도출

3. With Recursive 예시(1부터 5까지 출력)

with recursive cte as (
	# 1. 단순히 n 컬럼에 1만 출력한다.(항 1개)
    select 1 as n
    
    # 2. union all을 사용해 합집합을 구성하는데 중복되는 항도 출력한다.
    union all
	
    # 3. 임시 테이블 cte에 있는 항에서 1을 더한 값을 출력한다.
    # 4. 3번을 실행할 경우, cte에 1, 2가 삽입되어 있고, 2를 참조하여 + 1을 실행한다.
    # 5. 4번을 실행할 경우, cte에 1, 2, 3이 삽입되어 있고, 3을 참조하여 + 1을 실행한다.
    # 6. n이 5보다 작을 때까지 반복
    select n+1 
    from cte 
    where n < 5)

select * from cte;

Java, C++, Python 등 재귀함수 안에 들어가는 종료 조건(ex: if a == 0: break)이 먼저 나온다는 점에 주목하고 있다.

검사하는 조건은 union all 집합 연산자 다음 쿼리에 들어가 있다.

 

4. Employee 테이블의 Empno, Ename, Mgr, Path, Level을 계층 구조로 출력

WITH RECURSIVE cte AS
(
    # Non-Recursive query 가 필수로 있어야 함
    # 일종의 종료, 초기 조건(if a==0: break)
    SELECT empno, ename, mgr, 
    # CAST 함수(형 변환할 때 주로 사용하지만, 여기서는 컬럼의 속성을 변경하는데 사용되었다.)
    # 앞으로 문자열의 길이가 늘어날 것을 예상하여 사용
    # 시작 Root Node의 Level은 1로 지정한다.
    CAST(ename AS CHAR(200)) AS path, 1 AS lvl 
    FROM employee WHERE mgr=0
    
    # 합집합 연산자
    UNION
    
    SELECT e.empno, e.ename, e.mgr,
    # CONCAT: 문자열 합성 연산자, 기존 이름에 새로운 이름을 더하여 Path를 제작
    CONCAT(p.path,',',e.ename) AS path, p.lvl+1 AS lvl 
    FROM cte p JOIN employee e ON p.empno=e.mgr
)   
# 위의 내용은, 일단 최상위 부모 레코드를 찾아서 집합을 생성하고 
# 각 부모 레코드의 하위 레코드를 찾아서 부모 행의 path 컬럼 값을 하위 행의 path에 포함하도록 함
# 화면에 트리 구조로 표현하기 위해 공백 문자를 차수(level)에 해당하는 수만큼 덧붙인다
# 바깥에 선언된 WITH절의 이름을 반드시 사용하는 query도 필수
# REPEAT(a, b): a를 b만큼 반복
SELECT empno, CONCAT(REPEAT(' ', lvl*4), ename) ename, mgr, path, lvl 
FROM cte 
# path 임시컬럼을 사용하여 정렬하면 계층구조로 정렬된다
ORDER BY path;

쿼리 실행 결과

5. BBS(게시판) 테이블을 제작하고, 임시 데이터를 입력한 후, 계층 구조로 출력

* 테이블 구조

create table bbs (
	num int primary key auto_increment,
    title varchar(50),
    writer varchar(50),
    wdate timestamp(3),
    contents varchar(1000),
    hit int default 0,
    pnum int default 0 References num);

* 임시 데이터 입력

insert into bbs values(NULL, 'Hello World!', 'smith', now(3), 'hello everyone! This is Smith', 0, 0),
(NULL, 'Java FrameWork', 'leo', now(3), 'java framework has so many frameworks for users. for example spring makes..', 3, 1),
(NULL, 'sleepy', 'kali', now(3), 'after having lunch, maybe you feel sleepy. Yes. Me too', 0, 2),
(NULL, 'Coffee', 'blak', now(3), 'i look your post, how about taking a coffee?', 0, 3),
(NULL, 'Snack!!!', 'ali baba', now(3), 'except coffee, i sugges any chewing gum. coffee makes your body tired.', 0, 3),
(NULL, 'So cute', 'timo', now(3), 'timo timo timo is very very cute and strong. R is very beautiful! Boom is art!', 0, 3),
(NULL, 'Anywere', 'jinx', now(3), 'I hate timo.', 2, 6)
;

원래 게시글 데이터를 일괄적으로 삽입하기 보다, 하나하나 넣는 것이 wdate를 다양하게 해줄 수 있다.

* 계층 구조로 출력

WITH RECURSIVE tmp_bbs as (
	select num , title, pnum, 
    cast(writer as char(100)) as path, 1 as lvl 
	from bbs where pnum = 0
	union
	select b.num, b.title, b.pnum, concat(p.path, ",", b.writer) as path, p.lvl+1 as lvl
	from tmp_bbs p join bbs b on p.num = b.pnum
)
select concat(repeat(' ', lvl*4), num) as num, title, pnum, path, lvl
from tmp_bbs;

---

6. 세션 변수: DB에 접속하여 끊어질 때까지 유지되는 변수

예시) SET @age := 0;
사용) SELECT @age:=@age+1 age;

 

* 세션변수를 활용해 행 번호 제작

set @row := 0;
select @row := @row +1 "row", ename from employee;

* 서브쿼리가 먼저 실행되는 것을 확인하는 쿼리

select @row := @row +1 "row", ename from employee, (select @row:=100) t;

7. 세션 변수를 활용해 부모, 자식글 출력

* 테이블 형태

select * from employee;

WITH RECURSIVE cte AS (
  SELECT     empno,                          -- # Non-Recursive query 가 필수로 있어야 함
             CAST(ename AS CHAR(100)) ename, -- 이름 왼쪽에 들여쓰기 공백을 추가하기 위함
             mgr,
             hiredate,
             1 AS lvl,                       -- 최상위 부모 행으로부터의 차수(1부터 시작)
             @rn:=(@rn+1) AS pnum            -- 최상위 부모 행들의 행번호(1부터 증가함)
  FROM        (
            SELECT * FROM employee ORDER BY hiredate DESC -- 최신글 순으로 부모 행 정렬
          )t1, (SELECT @rn:=0)t2          -- 행번호로 사용할 변수(@rn) 선언
  WHERE      mgr = 0                         -- 최상위 행만을 선택함
  UNION                                      -- 아래는 바깥에 선언된 cte를 참조하는 커리(필수)
  SELECT     e.empno,
             CONCAT(REPEAT(' ', p.lvl*4), e.ename) ename,   -- 차수를 이용한 들여쓰기
             e.mgr,
             e.hiredate,
             p.lvl+1 AS lvl,                 -- 부모글의 차수에 1을 증가하여 자식 글의 차수 설정
             p.pnum AS pnum                  -- 자식 글에는 최상위 부모 글의 번호를 설정
  FROM       employee e
  INNER JOIN cte p
          ON p.empno = e.mgr
)
SELECT * FROM cte ORDER BY pnum, lvl, hiredate DESC;

---

8. Table Column명 변경 후, 부모글, 자식글 구조로 테이블 변경

* 컬럼명 변경(MySQL)

alter table bbs change pnum topid int;

일부터 시간을 변경했고, 정렬할 경우 num 순서는 1, 2, 6, 7, 3, 4, 5 가 나와야 한다.

9. 8번의 테이블을 게시글의 형태로 출력

WITH RECURSIVE cte AS (
	-- # Non-Recursive query 가 필수로 있어야 함
	SELECT num, title, CAST(writer AS CHAR(100)) writer, wdate, topid, 1 AS lvl,           
			@rn:=(@rn+1) AS pnum            -- 최상위 부모 행들의 행번호(1부터 증가함)
	FROM  (
			-- 최신글 순으로 부모 행 정렬
            SELECT * FROM bbs ORDER BY wdate DESC 
          ) t1, 
          -- 행번호로 사용할 변수(@rn) 선언
          (SELECT @rn:=0) t2
	-- 최상위 행만을 선택함
	WHERE topid = 0
	UNION                                      -- 아래는 바깥에 선언된 cte를 참조하는 커리(필수)
	SELECT b.num, b.title, CONCAT(REPEAT('　', p.lvl*4), b.writer) writer, b.wdate, b.topid, 
		   -- 부모글의 차수에 1을 증가하여 자식 글의 차수 설정
           p.lvl+1 AS lvl, p.pnum AS pnum                  -- 자식 글에는 최상위 부모 글의 번호를 설정
	FROM bbs b INNER JOIN cte p
	ON p.num = b.topid
)
SELECT * FROM cte ORDER BY pnum, lvl, wdate DESC;

공백의 경우 공백 특수문자를 넣어야, HTML에서 공백을 인식한다.

HTML에서 공백(스페이스바) 1개와 공백(스페이스바) 100개는 같은 것으로 인식하기 때문에, 특수문자를 삽입해야한다.