CREATE TABLE `baseaddress` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`pid` int(11) DEFAULT NULL
PRIMARY KEY (`id`)
)
//id以下查询
DROP PROCEDURE IF EXISTS proc_baseaddressList;
CREATE PROCEDURE proc_baseaddressList(IN temppid INT,IN ndepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT id FROM baseaddress WHERE pid=temppid;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
INSERT INTO tmplist VALUES(NULL,temppid,ndepth);
SET @@max_sp_recursion_depth = 10;
OPEN cur1;
FETCH cur1 INTO b;
WHILE done = 0 DO
CALL proc_baseaddressList(b,ndepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END;
DROP PROCEDURE IF EXISTS show_baseaddressList;
CREATE PROCEDURE show_baseaddressList(IN temppid INT)
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmplist(sno INT PRIMARY KEY AUTO_INCREMENT,
id INT ,depth INT);
DELETE FROM tmplist;
SET @@max_sp_recursion_depth = 10;
CALL proc_baseaddressList(temppid,0);
SELECT baseaddress.* FROM tmplist,baseaddress WHERE tmplist.id=
baseaddress.id;
END
//id向上级查询
DROP FUNCTION IF EXISTS fun_baseaddress_up;
CREATE FUNCTION fun_baseaddress_up(in_menu_id INT) RETURNS VARCHAR(1000) CHARSET utf8
BEGIN
DECLARE resultStr VARCHAR(1000);
DECLARE tempStr VARCHAR(1000);
SET resultStr = '$';
SET tempStr = CAST(in_menu_id AS CHAR);
WHILE tempStr IS NOT NULL DO SET resultStr = CONCAT(resultStr, ',', tempStr);
SELECT GROUP_CONCAT(pid)
INTO tempStr
FROM baseaddress
WHERE pid <> id
AND FIND_IN_SET(id, tempStr) > 0;
END WHILE;
RETURN resultStr order by id;
END
使用方法
call show_baseaddressList(1);
SELECT * FROM baseaddress WHERE FIND_IN_SET(id, fun_baseaddress_up(5))
