1. 查询子节点使用存储过程(暂未验证)
delimiter //
drop PROCEDURE IF EXISTS showChildLst//
CREATE PROCEDURE showChildLst (IN rootid INT)
BEGIN
DECLARE Level int ;
drop TABLE IF EXISTS tmpLst;
CREATE TABLE tmpLst (
id int,
nLevel int,
sCort varchar(8000)
);
Set Level=0 ;
INSERT into tmpLst SELECT id,Level,ID FROM crm_presona_dk WHERE presona_pid=rootid;
WHILE ROW_COUNT()>0 DO
SET Level=Level+1 ;
INSERT into tmpLst
SELECT A.id,Level,concat(B.sCort,'-',A.id) FROM crm_presona_dk A,tmpLst B
WHERE A.presona_pid=B.id AND B.nLevel=Level-1 ;
END WHILE;
END;
//
delimiter ;
call showChildLst(2);
select * from tmpLst;
2. 查询父节点使用sql(验证可用)
数据表的结构:
id name parent_id
---------------------------
Home 0
About 1
Contact 1
Legal 2
Privacy 4
Products 1
Support 1
SELECT T2.id, T2.name
FROM (
SELECT
@r AS _id,
(SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 5, @l := 0) vars, --查询id为5的所有上级
table1 h
WHERE @r <> 0) T1
JOIN table1 T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC