比如表结构数据如下:
Table:Tree
ID Name ParentId
1 一级 0
2 二级 1
3 三级 2
4 四级 3
mysql中
//查询方法: 表中id为 Integer类型
select
*
from
tree
where
find_in_set(id,getChildLst(1,1));
--根据id查所有下级
select
*
from
tree
where
find_in_set(id,getChildLst(1,2));--根据id查所有上级
CREATE FUNCTION `getChildLst`(rootId int,direction int) RETURNS varchar(4000) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(5000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd =cast(rootId as CHAR);
IF direction=1 THEN
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(id) INTO sTempChd FROM Tree where FIND_IN_SET(ParentId,sTempChd)>0;
END WHILE;
ELSEIF direction=2 THEN
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(ParentId) INTO sTempChd FROM Tree where FIND_IN_SET(Id,sTempChd)>0;
END WHILE;
END IF;
RETURN sTemp;
END
//查询方法: 表中id为 String类型 36位uuid
表结构
ccms_menu_func_tree
主键 节点名 上级节点名
node_id node_name up_node_id
//查询方法:
select * from ccms_menu_func_tree where find_in_set(node_id,getChildLst('id',1));--下查
select * from ccms_menu_func_tree where find_in_set(node_id,getChildLst('id',2));--上查
根据下级递归查询上级菜单 direction 1 下查 2上查
CREATE FUNCTION getMenuChildLst(nodeId VARCHAR(36),direction int)RETURNS varchar(4000) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(5000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd =cast(nodeId as CHAR);
IF direction=1 THEN
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(node_id) INTO sTempChd FROM ccms_menu_func_tree where FIND_IN_SET(up_node_id,sTempChd)>0;
END WHILE;
ELSEIF direction=2 THEN
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(up_node_id) INTO sTempChd FROM ccms_menu_func_tree where FIND_IN_SET(node_id,sTempChd)>0;
END WHILE;
END IF;
RETURN sTemp;
END
ORACLE中的查询方法:
1 2 3 4 5 6 7 8 9 |
|
可参见相关文章:http://blog.csdn.net/super_marioli/article/details/6253639