mysql递归查询

文章参考地址:

http://blog.sina.com.cn/s/blog_8edc37a80101ij6c.html

https://blog.youkuaiyun.com/weixin_35756522/article/details/81876534

 

需调整的参数:
    table:表名
    parent_id:父级分类id字段名
    #{categoryId}:传入的分类id
 

 

根据输入的分类查询所有子级分类

SELECT childrens.* FROM(
    SELECT
        @ids as _ids,
        (   SELECT @ids := GROUP_CONCAT(id)
            FROM `table`
            WHERE FIND_IN_SET(parent_id, @ids)			//parent_id 为父级分类id的字段名,需根据自己的表结构调整名字
        ) as cids,
        @l := @l+1 as level
    FROM `table`,
        (SELECT @ids :=#{categoryId}, @l := 0 ) b				//@ids :='#{此处为需要填入的分类id}'
    WHERE @ids IS NOT NULL
) parent, `table` childrens
WHERE FIND_IN_SET(childrens.id, parent._ids)

以上方法在mysql8.0.22中无效,下面的方法可行

with RECURSIVE 
  bomList AS (
	SELECT parent_id,child_id,1 AS level from table WHERE id=#{categoryId}		//'#{此处为需要填入的分类id}'
	UNION
	SELECT f.parent_id,f.child_id,p.level+1 AS level
	from table f
	INNER JOIN bomList p ON p.child_id=f.parent_id 
	)SELECT parent_id _ids,GROUP_CONCAT(child_id) cids,level FROM bomList GROUP BY parent_id,level order by LEVEL 

 

 

根据输入的分类查询所有父级分类

SELECT T2.*
    FROM ( 
        SELECT 
                @r AS _id, 
                (SELECT @r := parent_id FROM `table` WHERE id = _id) AS parent_id, 	//parent_id 为父级分类id的字段名,需根据子级的表结构调整名字
                 @l := @l + 1 AS lvl 
        FROM 
                (SELECT @r := #{categoryId}, @l := 0) vars, 	//@r :='#{此处为需要填入的分类id}'
                `table` h 
        WHERE @r <> 0) T1 
    JOIN `table` T2 
    ON T1._id = T2.id
ORDER BY id;

with RECURSIVE 
  bomList AS (
	SELECT id,child_id,1 AS level from bom_material_form WHERE child_id=#{categoryId}		//#{此处为需要填入的分类id}'
	UNION
	SELECT f.id,f.child_id,p.level+1 AS level
	from bom_material_form f
	INNER JOIN bomList p ON p.id=f.child_id 
	)SELECT child_id _id,GROUP_CONCAT(id) parent_id,level FROM bomList GROUP BY child_id,level order by LEVEL 

 

记录以备后期使用