[MySQL 8.0] WITH RECURSIVE 써서 계층형 쿼리 만들기

DB/MySQL

[MySQL 8.0] WITH RECURSIVE 써서 계층형 쿼리 만들기

배고파요 2024. 5. 17. 15:47

728x90

📍 오라클에서는 start with ~ connect by 로 계층형 쿼리를 만들 수 있는데... ㅠㅠ mysql 은 안됨.

📍 재귀를 이용해서 계층형 쿼리를 만들기!

CREATE TABLE `menu` (
  `menu_id` int NOT NULL,
  `menu_name` varchar(45) DEFAULT NULL,
  `role` varchar(45) DEFAULT NULL,
  `status` varchar(1) DEFAULT NULL,
  `par_menu_id` varchar(45) DEFAULT NULL,
  `menu_path` varchar(45) DEFAULT NULL,
  `order_num` int DEFAULT NULL,
  `reg_date` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`menu_id`)
)

INSERT INTO `menu` VALUES 
(1,'관리자','admin','1',NULL,NULL,1,'2024-04-23 11:38:22')
,(2,'사용자','user','1',NULL,NULL,2,'2024-04-23 12:41:12')
,(3,'영화','user','1','2','/movie/list',1,'2024-04-23 12:28:54')
,(4,'예매','user','1','2','/booking/list',2,'2024-04-23 12:41:12')
,(5,'극장','user','1','2','/theater/list',3,'2024-04-23 12:41:12')
,(6,'전체영화','user','1','3','/movie/list',2,'2024-04-27 05:06:24')
,(7,'예정','user','1','2',NULL,1,'2024-04-29 02:53:17')
,(8,'일간','user','1','7',NULL,1,'2024-04-29 03:00:53')
,(9,'주간','user','1','7',NULL,2,'2024-04-29 03:26:15');

WITH RECURSIVE cte AS (
	SELECT menu_id, menu_name, role, status, par_menu_id, menu_path, order_num,
		   CAST(CONCAT(order_num, '_', menu_id) as CHAR(40)) AS v_ord,
		   1 AS lvl
	FROM menu 
	WHERE par_menu_id IS NULL AND status = '1' 
	AND role = 'user'
	UNION ALL
	SELECT t.menu_id, t.menu_name, t.role, t.status, t.par_menu_id, t.menu_path, t.order_num,
		   CONCAT(cte.v_ord, '->', t.order_num, '_', t.menu_id) AS v_ord,
		   cte.lvl + 1
	FROM menu t
	INNER JOIN cte ON t.par_menu_id = cte.menu_id
	where t.status = '1'
)
SELECT * FROM cte
WHERE par_menu_id IS NOT NULL 
ORDER BY v_ord

📍 결과 ?

+) 📍응용!

응용 1. status 컬럼을 이용해서, "사용", "미사용"을 구문해서 사용하고 싶음.
- 최상단 부모가 "미사용" 이라면 그 이하의 자식들은 모두 미사용으로 간주.
- 1: 사용, 9: 미사용

--> 쿼리 결과

응용 2. order_num 컬럼을 이용해서, 하위 메뉴의 순서를 정하고 싶음.
- 관리자 페이지에서 원할 때마다 수정하고 싶음.
- 나중에 만든 메뉴가 더 앞에 보이게 하고 싶을 수도 있으니까!
  - CONCAT(cte.v_ord, '->', t.order_num, '_', t.menu_id) AS v_ord
  - 1. 재귀로 돌아온 정렬순서 -> 2.설정한 정렬 순서 _ 메뉴id
    - 정렬순서 _ 메뉴id 로 하는 이유는,,
      사람이 설정하다보니 같은 정렬순서를 넣는 경우도 있을 수 있으니까..