简介:mysql5.0.94版本,该版本以及较高级的版本(5.5、6等等)尚未支持循环递归查询,和sqlserver、oracle相比,mysql难于在树状表中层层遍历的子节点。本程序重点参考了下面的资料,写了两个sql存储过程,子节点查询算是照搬了,父节点查询是逆思维弄的。
-
资料参考:http://blog.csdn.net/ACMAIN_CHM/article/details/4142971#comments
表结构和表数据就不公示了,查询的表user_role,主键是id,每条记录有parentid字段(对应该记录的父节点,当然,一个父节点自然会有一个以上的子节点嘛)
1234567891011121314151617181920212223242526272829303132333435363738CREATE
FUNCTION
`getChildList`(rootId
INT
)
RETURNS
varchar
(1000)
BEGIN
DECLARE
sChildList
VARCHAR
(1000);
DECLARE
sChildTemp
VARCHAR
(1000);
SET
sChildTemp =
cast
(rootId
as
CHAR
);
WHILE sChildTemp
is
not
null
DO
IF (sChildList
is
not
null
)
THEN
SET
sChildList = concat(sChildList,
','
,sChildTemp);
ELSE
SET
sChildList = concat(sChildTemp);
END
IF;
SELECT
group_concat(id)
INTO
sChildTemp
FROM
user_role
where
FIND_IN_SET(parentid,sChildTemp)>0;
END
WHILE;
RETURN
sChildList;
END
;
/*获取子节点*/
/*调用: 1、select getChildList(0) id; 2、select * 5From user_role where FIND_IN_SET(id, getChildList(2));*/
CREATE
FUNCTION
`getParentList`(rootId
INT
)
RETURNS
varchar
(1000)
BEGIN
DECLARE
sParentList
varchar
(1000);
DECLARE
sParentTemp
varchar
(1000);
SET
sParentTemp =
cast
(rootId
as
CHAR
);
WHILE sParentTemp
is
not
null
DO
IF (sParentList
is
not
null
)
THEN
SET
sParentList = concat(sParentTemp,
','
,sParentList);
ELSE
SET
sParentList = concat(sParentTemp);
END
IF;
SELECT
group_concat(parentid)
INTO
sParentTemp
FROM
user_role
where
FIND_IN_SET(id,sParentTemp)>0;
END
WHILE;
RETURN
sParentList;
END
;
/*获取父节点*/
/*调用: 1、select getParentList(6) id; 2、select * From user_role where FIND_IN_SET(id, getParentList(2));*/