mysql 递归查询语句_写一个mysql数据库的sql 递归查询，我现在有个能查询三级的，谁能帮我改为查询四级或五级的...

最新推荐文章于 2021-04-14 16:33:19 发布

SonIC Lab

最新推荐文章于 2021-04-14 16:33:19 发布

阅读量132

点赞数

文章标签： mysql 递归查询语句

本文链接：https://blog.csdn.net/weixin_29894211/article/details/113213899

版权

CREATE TABLE `tablea` (

`cid` int(10) unsigned NOT NULL,

`pid` int(10) unsigned NOT NULL,

`name` varchar(45) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=gb2312

insert into `test`.`tablea`

(cid, pid, name)

values

(1 ,0 , '1 '),

(10 ,1 , '10 '),

(11 ,1 , '11 '),

(12 ,1 , '12 '),

(2 ,0 , '2 '),

(20 ,2 , '20 '),

(21 ,2 , '21 '),

(22 ,2 , '22 '),

(110, 10, '110'),

(120, 10, '120')

DELIMITER $$;

DROP PROCEDURE IF EXISTS `p_opt_getCount`$$

CREATE PROCEDURE `p_opt_getCount`(

in in_sql varchar(65532),

out out_count int)

BEGIN

-- get count from a select

DROP TABLE IF EXISTS xxx_t_count;

CREATE TEMPORARY TABLE xxx_t_count(num int) TYPE = HEAP;

set @sql = concat('insert xxx_t_count select count(*) from (', in_sql, ') a ');

PREPARE s1 FROM @sql;

EXECUTE s1;

DEALLOCATE PREPARE s1;

select num into out_count from xxx_t_count;

-- drop temporary table for releasing memory

DROP TABLE IF EXISTS xxx_t_count;

END$$

DELIMITER ;$$

-- 递归查找

DELIMITER $$;

DROP PROCEDURE IF EXISTS `p_opt_recursion`$$

CREATE PROCEDURE `p_opt_recursion`(

in in_table_name varchar(45), -- 目标表名

in child_column_name varchar(45), -- 子字段名

in parent_column_name varchar(45), -- 父字段名

in in_start_id int, -- 从那个值的父结点开始递归

in tmp_table_name varchar(45)) -- 自定义一个表名，用来得到递归的结果

BEGIN

DECLARE v_level INT default 0;

DECLARE v_count INT default 0;

DECLARE v_sql varchar(65532) default '';

-- temporary table doesn't support self-join

DROP TABLE IF EXISTS xxx_t_recursion;

CREATE TEMPORARY TABLE xxx_t_recursion(cid int, pid int, level int, INDEX `index_1`(`level`)) TYPE = HEAP;

DROP TABLE IF EXISTS xxx_t_recursion2;

CREATE TEMPORARY TABLE xxx_t_recursion2(cid int, pid int, level int, INDEX `index_1` (`level`)) TYPE = HEAP;

INSERT xxx_t_recursion(cid,pid,level) select in_start_id,in_start_id,v_level;

set v_sql = concat(' select a.',parent_column_name,' from ',in_table_name,' a, xxx_t_recursion b where a.',parent_column_name,' = b.cid and b.level = 0 ');

call p_opt_getCount(v_sql,v_count);

-- select v_count;

INSERT INTO xxx_t_recursion2 SELECT * FROM xxx_t_recursion;

WHILE v_count > 0 DO

SET v_level = v_level + 1;

set @sql = concat(' insert xxx_t_recursion(cid,pid,level) SELECT a.',child_column_name,',a.',parent_column_name,', ',v_level,' from ',in_table_name,' a, xxx_t_recursion2 b where a.',parent_column_name,' = b.cid and b.level = ',v_level,' - 1 ');

PREPARE s1 FROM @sql;

EXECUTE s1;

DEALLOCATE PREPARE s1;

set v_sql = concat(' select a.',parent_column_name,' from ',in_table_name,' a, xxx_t_recursion b where a.',parent_column_name,' = b.cid and b.level = ',v_level);

-- get v_sql record count

call p_opt_getCount(v_sql,v_count);

INSERT INTO xxx_t_recursion2 SELECT * FROM xxx_t_recursion;

END WHILE;

delete from xxx_t_recursion where level = 0;

SELECT * FROM xxx_t_recursion;

-- create temporary table named by tmp_table_name value

-- put the result in the new_temporary table

set @sql = concat('DROP TABLE IF EXISTS ',tmp_table_name);

PREPARE s1 FROM @sql;

EXECUTE s1;

DEALLOCATE PREPARE s1;