Mysql和oracle中的递归层次查询（父子查询）

描述：最近遇到了一个问题，在mysql中如何完成节点下的所有节点或节点上的所有父节点的查询？ 在Oracle中我们知道有一个Hierarchical Queries可以通过CONNECT BY来查询，但是，在MySQL中还没有对应的函数!!! 下面给出一个function来完成的方法 下面是sql脚本，想要运行的直接赋值粘贴进数据库即可。

好记性不如烂笔头

Mysql

下面给出一个function来完成的方法

下面是sql脚本，想要运行的直接赋值粘贴进数据库即可。

创建表treenodes（可以根据需要进行更改）

1.准备表

DROP TABLE IF EXISTS `ccms_menu_func_tree`;
CREATE TABLE `ccms_menu_func_tree` (
  `node_id` varchar(36) NOT NULL COMMENT '节点ID',
  `node_code` varchar(50) NOT NULL COMMENT '节点编码',
  `node_name` varchar(240) NOT NULL COMMENT '节点名称',
  `up_node_id` varchar(36) NOT NULL DEFAULT '-1' COMMENT '父级节点ID',
  `node_type` varchar(36) NOT NULL COMMENT '节点类型：menu-菜单,function-功能,button-按钮',
  `enabled` varchar(1) NOT NULL COMMENT '启用状态：Y-启用，N-禁用',
  `node_seq` varchar(2000) DEFAULT NULL COMMENT '节点层级序列',
  `app_id` varchar(36) NOT NULL COMMENT '所属应用ID',
  `real_addr` varchar(500) DEFAULT NULL COMMENT '实际地址',
  `node_url` varchar(240) DEFAULT NULL COMMENT '功能URL',
  `node_icon` varchar(200) DEFAULT NULL COMMENT '图标',
  `node_desc` text COMMENT '说明',
  `sort_no` int(11) DEFAULT NULL COMMENT '排序号',
  `created_by` bigint(20) DEFAULT NULL COMMENT '创建人ID',
  `creation_date` datetime DEFAULT NULL COMMENT '创建时间',
  `last_update_date` datetime DEFAULT NULL COMMENT '最后更新时间',
  `last_updated_by` bigint(20) DEFAULT NULL COMMENT '最后更新人ID',
  `created_by_name` varchar(50) DEFAULT NULL COMMENT '创建人',
  `tenant_id` varchar(36) DEFAULT NULL COMMENT '租户ID',
  `leaf_node` varchar(1) DEFAULT NULL COMMENT '是否为叶子节点:Y-是，N-否',
  `ext_param` text COMMENT '扩展参数（以JSON格式存储数据），使用场景（如获取待办数量角标的请求地址）。',
  `lowest_version` varchar(20) DEFAULT NULL COMMENT 'APP的最低适配版本号',
  `default_auth` varchar(1) DEFAULT NULL COMMENT '是否为APP端默认授权节点:Y-是，N-否',
  `node_label` varchar(140) DEFAULT NULL COMMENT '节点标签',
  `open_target` varchar(1) DEFAULT '1' COMMENT '1-内部打开，2-弹出新页签，3-弹出新窗口',
  PRIMARY KEY (`node_id`),
  KEY `fk_ccms_menu_func_tree_1` (`app_id`),
  CONSTRAINT `fk_ccms_menu_func_tree_1` FOREIGN KEY (`app_id`) REFERENCES `ccms_apps` (`app_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='菜单功能注册表';

-- ----------------------------
-- Records of ccms_menu_func_tree
-- ----------------------------
INSERT INTO `ccms_menu_func_tree` VALUES ('08bfc662d7fb468598fe93b7e4554308', 'kjcx_prj_0101', '项目立项申请', '3f490491db6c4469bbf95c15a34e3db0', 'fb5c03b5e524455888c4eb52cbcf1baa', 'Y', '.3f490491db6c4469bbf95c15a34e3db0.08bfc662d7fb468598fe93b7e4554308.', 'fe743975135d40c2816ca44e993551ff', '/layout/frame', '/projectManage/approval_list', '', '项目立项申请', '1', '1', '2019-10-25 17:06:50', '2020-02-16 12:21:02', '1', '系统管理员', null, null, null, null, null, null, null);
INSERT INTO `ccms_menu_func_tree` VALUES ('0b989f9279b447829a0ab6996519a3ed', 'kjcx_prj_0102', '项目立项审批', '3f490491db6c4469bbf95c15a34e3db0', 'fb5c03b5e524455888c4eb52cbcf1baa', 'Y', '.3f490491db6c4469bbf95c15a34e3db0.0b989f9279b447829a0ab6996519a3ed.', 'fe743975135d40c2816ca44e993551ff', '/layout', '/projectManage/approval_detail', '', '项目立项审批', '2', '1', '2019-10-25 17:07:37', '2020-02-16 12:21:07', '1', '系统管理员', null, null, null, null, null, null, null);
INSERT INTO `ccms_menu_func_tree` VALUES ('17ecac00216747f1a44daa0ef08f38b2', 'kjcx_prj_0106', '立项统计查询', '3f490491db6c4469bbf95c15a34e3db0', 'fb5c03b5e524455888c4eb52cbcf1baa', 'Y', '.3f490491db6c4469bbf95c15a34e3db0.17ecac00216747f1a44daa0ef08f38b2.', 'fe743975135d40c2816ca44e993551ff', '/layout/frame', '/projectManage/approval_statistics', '', '立项统计查询', '6', '1', '2019-10-31 09:56:03', '2020-02-10 17:24:28', '1', '系统管理员', null, null, null, null, null, null, null);
INSERT INTO `ccms_menu_func_tree` VALUES ('28dd1d30b29d48fd8e71001c29be8769', 'kjcx_prj_0103', '项目立项查询', '3f490491db6c4469bbf95c15a34e3db0', 'fb5c03b5e524455888c4eb52cbcf1baa', 'Y', '.3f490491db6c4469bbf95c15a34e3db0.28dd1d30b29d48fd8e71001c29be8769.', 'fe743975135d40c2816ca44e993551ff', '/layout', '/projectManage/approval_listQuery', '', '项目立项查询', '3', '1', '2019-10-25 17:08:10', '2020-02-16 12:21:18', '1', '系统管理员', null, null, null, null, null, null, null);
INSERT INTO `ccms_menu_func_tree` VALUES ('3f490491db6c4469bbf95c15a34e3db0', 'kjcx_prj_01', '项目立项管理', '-1', 'd04194db50a745beabf012e36fce19dd', 'Y', '.3f490491db6c4469bbf95c15a34e3db0.', 'fe743975135d40c2816ca44e993551ff', '/layout/frame', '', '', '项目立项管理', '1', '1', '2019-10-25 17:04:33', '2020-02-10 17:22:44', '1', '系统管理员', null, null, null, null, null, null, null);
INSERT INTO `ccms_menu_func_tree` VALUES ('86dfe29184bd4da38727c8b612c80675', 'kjcx_prj_0104', '资本支出计划', '3f490491db6c4469bbf95c15a34e3db0', 'fb5c03b5e524455888c4eb52cbcf1baa', 'Y', '.3f490491db6c4469bbf95c15a34e3db0.86dfe29184bd4da38727c8b612c80675.', 'fe743975135d40c2816ca44e993551ff', '/layout', '/projectManage/xfTable', '', '资本支出计划', '4', '1', '2019-10-25 17:08:47', '2020-02-16 12:21:21', '1', '系统管理员', null, null, null, null, null, null, null);
INSERT INTO `ccms_menu_func_tree` VALUES ('f6df9e5672984639a9abeb925b952c06', 'kjcx_prj_0105', '项目立项合并', '3f490491db6c4469bbf95c15a34e3db0', 'fb5c03b5e524455888c4eb52cbcf1baa', 'Y', '.3f490491db6c4469bbf95c15a34e3db0.f6df9e5672984639a9abeb925b952c06.', 'fe743975135d40c2816ca44e993551ff', '/layout', '/projectManage/approval/proMerger', '', '项目立项合并', '5', '1', '2019-10-25 17:09:54', '2020-02-16 12:21:25', '1', '系统管理员', null, null, null, null, null, null, null);

 

2.写个函数function

DROP FUNCTION IF EXISTS getAreaChildDemo;
# 入参 nodeId 主键（此处为字符串类型可根据业务需求设置int类型）   direction查询方式  1从上往下递归查   2从下往上递归查
CREATE FUNCTION getAreaChildDemo(nodeId VARCHAR(36),direction INT)
RETURNS VARCHAR(4000)
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

3.查询方法

select * from ccms_menu_func_tree t ;
-- 递归查询下级
select * from ccms_menu_func_tree t   where find_in_set(t.node_id,getChildLst('3f490491db6c4469bbf95c15a34e3db0', 1));
-- 递归查询上级
select * from ccms_menu_func_tree t   where find_in_set(t.node_id,getChildLst('08bfc662d7fb468598fe93b7e4554308', 2));

 

参照

https://blog.csdn.net/weixin_41986096/article/details/89216117

https://blog.csdn.net/zhangpengself520/article/details/77451805

https://blog.csdn.net/qq_27471405/article/details/83008896?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_baidulandingword-9&spm=1001.2101.3001.4242

https://blog.csdn.net/lilizhou2008/article/details/108505353?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_title-12&spm=1001.2101.3001.4242

 

 oracle11  使用plsql操作

oracle中 connect by prior 递归算法


 自顶向下，自下向上口诀:

 start with child_id = 10 connect by (prior 
 child_id) = 
  parent_id

 prior 和 子列在一起，表示寻找它的子孙，即自顶向下，和父列在一起，表示开始寻找她的爸爸，即自下向上。

1.创建一个测试表
 

create table cux_hr_test_sc_menu( 
menu_id varchar2(32),
parent_id    varchar2(32));

insert into cux_hr_test_sc_menu values ( '1', '' );

insert into cux_hr_test_sc_menu values ( '2', '1' );

insert into cux_hr_test_sc_menu values ( '3', '1' );

insert into cux_hr_test_sc_menu values ( '4', '2' );

insert into cux_hr_test_sc_menu values ( '5', '3' );

insert into cux_hr_test_sc_menu values ( '7', '' );
insert into cux_hr_test_sc_menu values ( '8', '7' );
commit;

查询结果

select * from cux_hr_test_sc_menu;

1            7
2  3         8
4  5
6

start with 递归查询起始条件

connect by 连接语句，后面加上连接的条件

为了演示向上和向下两个方向，我们这里选择menu_id为2的中间节点作为起始节点。

这里注意下PRIOR的位置，和menu_id放一起，表示向下查询

-- 向下查询

select  t.* from cux_hr_test_sc_menu t start with t.menu_id=2
connect by prior t.menu_id=t.parent_id ;

 

向上查询方式

这里PRIOR和parent_id放一起，表示按照父节点方向，向上查询


-- 向上查询  
select  *  from cux_hr_test_sc_menu t start with t.menu_id=2
connect by prior  t.parent_id=t.menu_id ;

 

以上的语句中，还可以添加where条件语句，这些条件是在递归查询的结果集中，再根据where的条件进行筛选的。

参考

https://blog.csdn.net/qq_33656602/article/details/77750576

https://jingyan.baidu.com/article/73c3ce2839e0c6e50343d9f8.html

 

 

 

 

 

 

版权声明：本文为weixin_35815479原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。