描述:最近遇到了一个问题,在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
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