用mysql客户端在库中建立函数queryOrgChildren(查找子节点)和queryOrgLevel(查看当前节点在树中的级别):
DROP FUNCTION IF EXISTS `queryOrgChildren`;
CREATE FUNCTION `queryOrgChildren`(id INT)
RETURNS varchar(2000)
BEGIN
DECLARE tempChd VARCHAR(2000);
SET tempChd = cast(id as char);
IF tempChd is not NULL THEN
SELECT group_concat(groupId) INTO tempChd FROM t_user_group TUG where TUG.PARENTUSERGROUPID=tempChd;
end if;
return tempChd;
END;
DROP FUNCTION IF EXISTS `queryOrgLevel`;
CREATE FUNCTION `queryOrgLevel`(id INT)
RETURNS int
BEGIN
DECLARE grouplevel int;
DECLARE tempPar int;
SET grouplevel=0,tempPar=0;
SELECT PARENTUSERGroupId INTO tempPar FROM t_user_group TUG where TUG.GROUPID=id;
WHILE tempPar != 0 DO
SET grouplevel=grouplevel+1;
SELECT PARENTUSERGroupId INTO tempPar FROM t_user_group TUG where TUG.GROUPID=tempPar;
END WHILE;
return grouplevel;
END;
当然用户可根据上边2个函数发挥,比如写获取所有子孙节点的函数和获取所有父类节点的函数,有可能还会用到FIND_IN_SET(str,strlist)函数