mysql树状查询的案例分析
发布时间：2022-01-12 11:04:21 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要为大家展示了mysql树状查询的示例分析，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下mysql树状查询的示例分析这篇文章吧。 --创建表 drop table if exists t_hierarchy; CREATE TABLE t_hierarch
|
这篇文章主要为大家展示了“mysql树状查询的示例分析”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“mysql树状查询的示例分析”这篇文章吧。 --创建表 drop table if exists t_hierarchy; CREATE TABLE t_hierarchy ( id int(10) unsigned NOT NULL AUTO_INCREMENT, parent int(10) unsigned NOT NULL, PRIMARY KEY (id), KEY ix_hierarchy_parent (parent, id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --创建存储过程插入数据 DROP PROCEDURE if exists prc_fill_hierarchy; DELIMITER $$ CREATE PROCEDURE prc_fill_hierarchy (level INT, fill INT) BEGIN DECLARE _level INT; DECLARE _fill INT; INSERT INTO t_hierarchy (id, parent) VALUES (1, 0); SET _fill = 0; WHILE _fill < fill DO INSERT INTO t_hierarchy (parent) VALUES (1); SET _fill = _fill + 1; END WHILE; SET _fill = 1; SET _level = 0; WHILE _level < level DO INSERT INTO t_hierarchy (parent) SELECT hn.id FROM t_hierarchy ho, t_hierarchy hn WHERE ho.parent = 1 AND hn.id > _fill; SET _level = _level + 1; SET _fill = _fill + POWER(fill, _level); END WHILE; END $$ DELIMITER ; --插入数据 START TRANSACTION; CALL prc_fill_hierarchy(3, 2); COMMIT; --创建生成树的函数 DELIMITER $$ CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT) RETURNS INT NOT DETERMINISTIC READS SQL DATA BEGIN DECLARE _id INT; DECLARE _parent INT; DECLARE _next INT; DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL; SET _parent = @id; SET _id = -1; IF @id IS NULL THEN RETURN NULL; END IF; LOOP SELECT MIN(id) INTO @id FROM t_hierarchy WHERE parent = _parent AND id > _id; IF @id IS NOT NULL OR _parent = @start_with THEN SET @level = @level + 1; RETURN @id; END IF; SET @level := @level - 1; SELECT id, parent INTO _id, _parent FROM t_hierarchy WHERE id = _parent; END LOOP; END $$ DELIMITER ; --生成tree的查询 SELECT CONCAT(REPEAT('--->', level - 1), CAST(hi.id AS CHAR)) AS treeitem, parent, level FROM ( SELECT hierarchy_connect_by_parent_eq_prior_id(id) AS id, @level AS level FROM ( SELECT @start_with := 0, @id := @start_with, @level := 0 ) vars, t_hierarchy WHERE @id IS NOT NULL ) ho JOIN t_hierarchy hi ON hi.id = ho.id 以上是“mysql树状查询的示例分析”这篇文章的所有内容，感谢各位的阅读！ （编辑：锡盟站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐