树 mysql_mysql树

最新推荐文章于 2024-06-03 00:49:12 发布

开东

最新推荐文章于 2024-06-03 00:49:12 发布

阅读量83

点赞数

文章标签：树 mysql

本文链接：https://blog.csdn.net/weixin_34255938/article/details/114020118

版权

http://blog.itpub.net/26230597/viewspace-1269668/

http://blog.csdn.net/mchdba/article/details/39277301

DROP FUNCTION IF EXISTS fn_tree_pathname;; CREATE FUNCTION fn_tree_pathname(nid INT,delimit VARCHAR(10)) RETURNS VARCHAR(2000) CHARSET utf8 BEGIN DECLARE pathid VARCHAR(1000); SET @pathid=''; CALL pro_cre_pnlist(nid,delimit,@pathid); RETURN @pathid; END; drop PROCEDURE pro_cre_pnlist; CREATE PROCEDURE pro_cre_pnlist(IN nid INT,IN delimit VARCHAR(10),INOUT pathstr VARCHAR(1000)) BEGIN DECLARE done INT DEFAULT 0; DECLARE parentid INT DEFAULT 0; DECLARE cur1 CURSOR FOR SELECT t.parent_node_id,CONCAT(t.node_name,delimit,pathstr) FROM tb_catalog_node AS t WHERE t.node_id = nid; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; SET max_sp_recursion_depth=12; OPEN cur1; FETCH cur1 INTO parentid,pathstr; WHILE done=0 DO CALL pro_cre_pnlist(parentid,delimit,pathstr); FETCH cur1 INTO parentid,pathstr; END WHILE; CLOSE cur1; END select * from tb_catalog_node; select fn_tree_pathname(t.NODE_ID,'/') pathname ,t3.OFR_NAME , t3.OFR_CODE from tb_catalog_node t INNER JOIN tb_catalog_ofr_rel t2 on t2.NODE_ID=t.NODE_ID INNER JOIN tb_prd_ofr t3 on t3.OFR_ID=t2.OFR_ID where t.NODE_ID=1000102;