MARIA DB 에서의 재귀함수, WITH RECURSIVE

행을 여러개 생성해야 할 때, 오라클은 CONNECT BY 라는 깔끔한 문장이 있다.

이렇게 말이지.

SELECT 
	A.*
FROM
   (
   SELECT 
   	LEVEL 
   FROM 
   	DUAL
   CONNECT BY LEVEL <= 24 -- LEVEL 24까지 생성
   ) A
;

 

---

 

하지만 MARIA DB, MYSQL에서는 재귀함수를 통해서 아래 행들을 부르고 부르고 또 불러야 한다. 언제까지? 내가 설정한 개수까지. WITH RECURSIVE로 시작하는 이 문장은, 처음에는 너무 어색했는데 자꾸 보다 보니까 조금 감이 생겼다.

 

WITH RECURSIVE A AS(
	SELECT 1 AS LEVEL -- 기본 레벨로 삼는다
	UNION ALL -- UNION ALL로 아래 생기는 것들을 모두 붙일것임
	SELECT 1 + LEVEL  -- 기본 1 + 레벨을 붙이므로 여기는 2부터 시작한다
	FROM A
	WHERE LEVEL < 10 -- 레벨이 9가 될때까지 이므로 총 1~10레벨이 생긴다.
) -- WITH RECURSIVE안에서 A라는것을 정의하는 것과 같음
SELECT
	*
FROM A;
-- 이렇게 하면 위에 있는 오라클 구문과 레벨만 다른 동일 결과가 나오므로, 다른 식으로 더 확장시켜 보겠음.


WITH RECURSIVE A AS(
	SELECT 1 AS LEVEL 
	UNION ALL 
	SELECT 1 + LEVEL
	FROM A
	WHERE LEVEL < 10 
)
SELECT
	R.FIRST
	, R.SECOND
	, R.RESULT
	, FLOOR(R.RESULT * 0.5) DIVIDED -- RESULT로 간단하게 가져다 쓰려고 INNER TABLE을 하나 더 올렸다
FROM 
	(
	SELECT 
		T.N1 FIRST
		, T.N2 SECOND
		, T.N1 + T.N2 RESULT -- 여기서는 바로 RESULT값을 쓰지 못하고 (T.N1+T.N2)*0.5로 해줘야 함. SELECT문은 가로로 붙기 때문
	FROM 
		(
		SELECT
			ROUND((RAND() *2 +1) * 100000) N1
			, ROUND((RAND() *3 +1) * 1000000) N2
		FROM
			A -- WITH RECURSIVE로 만든 행에다가 SELECT 를 붙여서 출력한다.
		) T
	) R
;

 

 

---

 

번외로,  ROW_NUMBER()를 가지고 다른 테이블에서 행 개수만 따오는 방법도 있다.

이 방법은 ORACLE, MARIADB계열 양쪽 모두에 가능하다.

SELECT
   ADDDATE('2023-01-25', INTERVAL (T.seq - 1) MONTH) AS SALARY_DATE
   , M.ID
FROM
   (
   SELECT
       R.*
   FROM
       (
       SELECT
           ROW_NUMBER() OVER (ORDER BY TEAM_ID) AS seq
       FROM TEAM
       LIMIT 24 -- TEAM 테이블에서 ROW NUMBER만 24까지 가져온다 (동시에 TEAM_ID로 정렬도 함)
       ) R
   ) T
   INNER JOIN MEMBER M ON 1=1 -- MEMBER 테이블과 카티션 조인을 함. 24개 * 멤버테이블 행개수만큼 곱한 결과가 나온다
ORDER BY M.ID, SALARY_DATE
;

 

* 이때 ROW_NUMBER는 행 개수 (정확히는 각 행의 순번)만 가져다 쓰는거고, TEAM 테이블 데이터를 가져오는게 아니다.