[MySQL] WITH RECURSIVE / 재귀쿼리 재귀호출 계층형쿼리

 

MySQL 버전: 8.0.35

 

 

댓글 테이블 ↓

 

 

댓글 테이블 데이터 예시 ↓

 

 

 

query ↓

select * from 
(WITH RECURSIVE CTS AS (
    SELECT comments.* , 0 as lvl , cast(comments.comment_id as char) as path
    FROM comments WHERE upper_id = 0 AND store_id = 1
    UNION ALL
    SELECT u.*, CTS.lvl+1, concat(CTS.path, ',', cast(u.comment_id as char))
    FROM comments u
    INNER JOIN CTS ON u.upper_id = CTS.comment_id
)
SELECT comment_id, user_id, recipe_id, post_id, store_id, contents, image, stars, stars_avg, reg_date, mdfy_date, like_cnt, upper_id, category, 
	lvl, cast(substring_index(path, ',', 1) as decimal) as group_id, path
from CTS 
ORDER BY group_id desc, path
) as t1 where t1.category = 6 and t1.store_id = 1;

 

 

 

결과 ↓

 

 

---

 

 

query 전문 ↓

select * from 
(
WITH RECURSIVE CTS AS (
    SELECT 
		comments.comment_id
		,comments.user_id
		,comments.recipe_id
		,comments.post_id
		,comments.store_id
		,comments.contents
		,comments.image
		,comments.stars
		,comments.stars_avg
		,comments.reg_date
		,comments.mdfy_date
		,comments.like_cnt
		,comments.upper_id
		,comments.category
		, 0 as lvl
        , cast(comments.comment_id as char) as path
    FROM comments
    WHERE upper_id = 0 AND store_id = 1
    UNION ALL
    SELECT  
		u.comment_id
		,u.user_id
		,u.recipe_id
		,u.post_id
		,u.store_id
		,u.contents
		,u.image
		,u.stars
		,u.stars_avg
		,u.reg_date
		,u.mdfy_date
		,u.like_cnt
		,u.upper_id
		,u.category
		,CTS.lvl+1
        ,concat(CTS.path,',',cast(u.comment_id as char))
    FROM comments u
    INNER JOIN CTS ON u.upper_id = CTS.comment_id
	WHERE u.store_id = 1
)
SELECT 
	comment_id
	,user_id
	,recipe_id
	,post_id
	,store_id
	,contents
	,image
	,stars
	,stars_avg
	,reg_date
	,mdfy_date
	,like_cnt
	,upper_id
	,category
    ,lvl
    ,cast(substring_index(path,',',1) as decimal) as group_id
    ,path
from CTS 
ORDER BY group_id desc, path
) as t1
where t1.category = 6;

 

 

users 테이블과 조인한 query ↓

select t2.uid, t1.* from 
(WITH RECURSIVE CTS AS (
    SELECT comments.* , 0 as lvl , cast(comments.comment_id as char) as path
    FROM comments WHERE upper_id = 0 AND store_id = 100147
    UNION ALL
    SELECT u.*, CTS.lvl+1, concat(CTS.path, ',', cast(u.comment_id as char))
    FROM comments u
    INNER JOIN CTS ON u.upper_id = CTS.comment_id
)
SELECT comment_id, user_id, recipe_id, post_id, store_id, contents, image, stars, stars_avg, reg_date, mdfy_date, like_cnt, upper_id, category, 
	lvl, cast(substring_index(path, ',', 1) as decimal) as group_id, path
from CTS 
ORDER BY group_id desc, path) as t1 left join users t2 on t1.user_id = t2.user_id where t1.category = 6 and t1.store_id = 100147 and t1.lvl = 1;