MySQL中实现递归查询
MySQL中实现递归查询
对于数据库中的树形结构数据，如部门表，有时候，我们需要知道某部门的所有下属部分或者某部分的所有上级部门，这时候就需要用到mysql的递归查询
1、创建表
-
DROP TABLE IF EXISTS `t_areainfo`;
-
CREATE TABLE `t_areainfo` (
-
`id` int(11) NOT '0' AUTO_INCREMENT,
-
`level` int(11) DEFAULT '0',
-
`name` varchar(255) DEFAULT '0',
-
`parentId` int(11) DEFAULT '0',
-
`status` int(11) DEFAULT '0',
-
PRIMARY KEY (`id`)
-
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8;
2、初始数据
-
INSERT INTO `t_areainfo` VALUES ('1', '0', '中国', '0', '0');
-
INSERT INTO `t_areainfo` VALUES ('2', '0', '华北区', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('3', '0', '华南区', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('4', '0', '北京', '2', '0');
-
INSERT INTO `t_areainfo` VALUES ('5', '0', '海淀区', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('6', '0', '丰台区', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('7', '0', '朝阳区', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('8', '0', '北京XX区1', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('9', '0', '北京XX区2', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('10', '0', '北京XX区3', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('11', '0', '北京XX区4', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('12', '0', '北京XX区5', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('13', '0', '北京XX区6', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('14', '0', '北京XX区7', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('15', '0', '北京XX区8', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('16', '0', '北京XX区9', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('17', '0', '北京XX区10', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('18', '0', '北京XX区11', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('19', '0', '北京XX区12', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('20', '0', '北京XX区13', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('21', '0', '北京XX区14', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('22', '0', '北京XX区15', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('23', '0', '北京XX区16', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('24', '0', '北京XX区17', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('25', '0', '北京XX区18', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('26', '0', '北京XX区19', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('27', '0', '北京XX区1', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('28', '0', '北京XX区2', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('29', '0', '北京XX区3', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('30', '0', '北京XX区4', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('31', '0', '北京XX区5', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('32', '0', '北京XX区6', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('33', '0', '北京XX区7', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('34', '0', '北京XX区8', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('35', '0', '北京XX区9', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('36', '0', '北京XX区10', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('37', '0', '北京XX区11', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('38', '0', '北京XX区12', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('39', '0', '北京XX区13', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('40', '0', '北京XX区14', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('41', '0', '北京XX区15', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('42', '0', '北京XX区16', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('43', '0', '北京XX区17', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('44', '0', '北京XX区18', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('45', '0', '北京XX区19', '4', '0');
-
INSERT INTO `t_areainfo` VALUES ('46', '0', 'xx省1', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('47', '0', 'xx省2', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('48', '0', 'xx省3', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('49', '0', 'xx省4', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('50', '0', 'xx省5', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('51', '0', 'xx省6', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('52', '0', 'xx省7', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('53', '0', 'xx省8', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('54', '0', 'xx省9', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('55', '0', 'xx省10', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('56', '0', 'xx省11', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('57', '0', 'xx省12', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('58', '0', 'xx省13', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('59', '0', 'xx省14', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('60', '0', 'xx省15', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('61', '0', 'xx省16', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('62', '0', 'xx省17', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('63', '0', 'xx省18', '1', '0');
-
INSERT INTO `t_areainfo` VALUES ('64', '0', 'xx省19', '1', '0');
3、向下递归
利用find_in_set()函数和group_concat()函数实现递归查询：
-
DROP FUNCTION IF EXISTS queryChildrenAreaInfo;
-
DELIMITER ;;
-
CREATE FUNCTION queryChildrenAreaInfo(areaId INT)
-
RETURNS VARCHAR(4000)
-
BEGIN
-
DECLARE sTemp VARCHAR(4000);
-
DECLARE sTempChd VARCHAR(4000);
-
-
SET sTemp='$';
-
SET sTempChd = CAST(areaId AS CHAR);
-
-
WHILE sTempChd IS NOT NULL DO
-
SET sTemp= CONCAT(sTemp,',',sTempChd);
-
SELECT GROUP_CONCAT(id) INTO sTempChd FROM t_areainfo WHERE FIND_IN_SET(parentId,sTempChd)>0;
-
END WHILE;
-
RETURN sTemp;
-
END
-
;;
-
DELIMITER ;
4、调用方式
SELECT queryChildrenAreaInfo(1);
查询id为"4"下面的所有节点
SELECT * FROM t_areainfo WHERE FIND_IN_SET(id,queryChildrenAreaInfo(4));
5、向上递归
-
DROP FUNCTION IF EXISTS queryChildrenAreaInfo1;
-
DELIMITER;;
-
CREATE FUNCTION queryChildrenAreaInfo1(areaId INT)
-
RETURNS VARCHAR(4000)
-
BEGIN
-
DECLARE sTemp VARCHAR(4000);
-
DECLARE sTempChd VARCHAR(4000);
-
-
SET sTemp='$';
-
SET sTempChd = CAST(areaId AS CHAR);
-
SET sTemp = CONCAT(sTemp,',',sTempChd);
-
-
SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;
-
WHILE sTempChd <> 0 DO
-
SET sTemp = CONCAT(sTemp,',',sTempChd);
-
SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;
-
END WHILE;
-
RETURN sTemp;
-
END
-
;;
-
DELIMITER ;
6、调用方式
查询id为"7"的节点的所有上级节点：
SELECT * from t_areainfo where FIND_IN_SET(id,queryChildrenAreaInfo1(7));
7. demo 实战 （以DEV_ORGANIZE表为例）
（1）创建表
CREATE TABLE `DEV_ORGANIZE` (
`ID` varchar(36) NOT NULL COMMENT '主键',
`NAME` varchar(64) DEFAULT NULL COMMENT '名称',
`FLAG` int(11) DEFAULT NULL COMMENT '组织标记',
`ACTIVEFLAG` int(11) DEFAULT NULL COMMENT '激活标记',
`TYPE` int(11) DEFAULT NULL COMMENT '组织类型',
`DESCRIBE` varchar(256) DEFAULT NULL COMMENT '描述',
`TEL` varchar(32) DEFAULT NULL COMMENT '电话',
`FAX` varchar(32) DEFAULT NULL COMMENT '传真',
`WEBSITE` varchar(512) DEFAULT NULL COMMENT '网址',
`ADDRESS` varchar(256) DEFAULT NULL COMMENT '地址',
`POSTCODE` varchar(8) DEFAULT NULL COMMENT '邮政编码',
`CONTACT` varchar(16) DEFAULT NULL COMMENT '联系人',
`SYSCREATETIME` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建日期',
`SYSCREATOR` varchar(36) DEFAULT NULL COMMENT '创建用户',
`SYSTHEPOST` varchar(36) DEFAULT NULL COMMENT '所属岗位',
`SYSTHEORG` varchar(36) DEFAULT NULL COMMENT '所属组织',
`SYSUPDATETIME` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改日期',
`SYSUPDATER` varchar(36) DEFAULT NULL COMMENT '最后修改用户',
`LEVELCODE` varchar(256) DEFAULT NULL COMMENT '层次码',
`PARENTID` varchar(36) DEFAULT NULL COMMENT '父组织外键',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='组织机构';
（2）插入数据
INSERT INTO `DEV_ORGANIZE` VALUES ('00000000-0000-0000-0000-000000000000', '组织机构', '1', '1', '1', null, null, null, null, null, null, null, null, '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', null, '00000000-0000-0000-0000-000000000000', null, null);
INSERT INTO `DEV_ORGANIZE` VALUES ('402805815dfe7ea9015dfed4900f0000', '示范区', '1', '1', '0', '222', '010-6642758', null, null, '222', '000111', '11', '2017-08-20 16:48:50', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '2018-07-17 11:55:31', '00000000-0000-0000-0000-000000000000', null, '00000000-0000-0000-0000-000000000000');
INSERT INTO `DEV_ORGANIZE` VALUES ('40289348621d1ac8016222cb124d000e', '4444', '1', '1', '0', null, null, null, null, null, null, null, '2018-03-14 12:37:12', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '2018-03-14 12:37:12', '00000000-0000-0000-0000-000000000000', null, '5a2bb2e1755d400b865b74cd0a0ddac0');
INSERT INTO `DEV_ORGANIZE` VALUES ('40289348621d1ac8016222cb27ec000f', '55555', '1', '1', '0', null, null, null, null, null, null, null, '2018-03-14 12:37:17', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '2018-03-14 12:37:17', '00000000-0000-0000-0000-000000000000', null, '400503adc89d103abe1ae529120ff543');
INSERT INTO `DEV_ORGANIZE` VALUES ('40289348621d1ac8016222cb3f1b0010', '66666', '1', '1', '0', null, null, null, null, null, null, null, '2018-03-14 12:37:23', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '2018-03-14 12:37:23', '00000000-0000-0000-0000-000000000000', null, 'a894fdc5093e1ce99dd00ab6ec7f6a26');
INSERT INTO `DEV_ORGANIZE` VALUES ('40289348621d1ac8016222cb55ce0011', '777777', '1', '1', '0', null, null, null, null, null, null, null, '2018-03-14 12:37:29', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '2018-03-14 12:37:29', '00000000-0000-0000-0000-000000000000', null, 'af53b57ba8a697132a815aa2840b8b2e');
INSERT INTO `DEV_ORGANIZE` VALUES ('4028934862d7d8a40162f123afee0096', '22', '1', '1', '0', null, null, null, null, null, null, null, '2018-04-23 14:15:36', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '2018-04-23 14:15:36', '00000000-0000-0000-0000-000000000000', null, '5852ce0e825c42afec18186931c9a63a');
INSERT INTO `DEV_ORGANIZE` VALUES ('4028934862d8697a0162db82cf40002a', '1111111111111111111', '1', '1', '0', null, null, null, null, null, null, null, '2018-04-19 09:27:54', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '2018-04-19 09:27:54', '00000000-0000-0000-0000-000000000000', null, '9d0d40d78285462f9d721c55784d9f13');
INSERT INTO `DEV_ORGANIZE` VALUES ('402893815e927a17015e92c514fc0004', '部门5A', '1', '1', '1', null, null, null, null, null, null, null, '2017-09-12 12:54:58', null, null, null, null, null, null, 'a585160ac656a69dcbd97088d7b68edf');
INSERT INTO `DEV_ORGANIZE` VALUES ('402893815e927a17015e92c56e4f0005', '支部1', '0', '1', '0', null, null, null, null, null, null, null, '2017-09-18 10:16:25', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '2017-09-18 10:16:25', '00000000-0000-0000-0000-000000000000', null, 'd2ab550caf79668a2e56e4ef3537c674');
INSERT INTO `DEV_ORGANIZE` VALUES ('402893815e927a17015e92c601cf0006', '支部2', '0', '1', '0', null, null, null, null, null, null, null, '2017-09-18 10:17:03', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '2017-09-18 10:17:03', '00000000-0000-0000-0000-000000000000', null, 'd2ab550caf79668a2e56e4ef3537c674');
INSERT INTO `DEV_ORGANIZE` VALUES ('402893815e927a17015e92c6d5020007', '支部2', '1', '1', '0', null, null, null, null, null, null, null, '2017-09-18 10:17:57', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '2017-09-18 10:17:57', '00000000-0000-0000-0000-000000000000', null, 'd2ab550caf79668a2e56e4ef3537c674');
INSERT INTO `DEV_ORGANIZE` VALUES ('402893815ecc41f6015ecc423b6d0010', '11', '1', '1', '1', '1', null, null, null, null, null, null, '2017-09-18 10:17:57', null, null, null, null, null, null, null);
INSERT INTO `DEV_ORGANIZE` VALUES ('ff8080815e7080fd015e7400c42c0000', '黑龙江省农业科学院国家现代农业示范区', '1', '1', '0', null, null, null, null, null, null, null, '2017-09-12 10:54:02', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '2018-04-10 10:01:03', '00000000-0000-0000-0000-000000000000', null, '402805815dfe7ea9015dfed4900f0000');
INSERT INTO `DEV_ORGANIZE` VALUES ('ff8080815e7080fd015e74013bb40001', '四川省农业科学院现代农业科技创新示范园', '1', '1', '0', null, null, null, null, null, null, null, '2017-09-12 10:54:33', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '2018-04-10 10:01:19', '00000000-0000-0000-0000-000000000000', null, '402805815dfe7ea9015dfed4900f0000');
INSERT INTO `DEV_ORGANIZE` VALUES ('ff8080815e7080fd015e7417fdc00009', '壮壮1', '1', '1', '0', null, null, null, null, null, null, null, '2017-09-12 11:19:24', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '2017-09-12 11:19:24', '00000000-0000-0000-0000-000000000000', null, 'a585160ac656a69dcbd97088d7b68edf');
INSERT INTO `DEV_ORGANIZE` VALUES ('ff8080815e7080fd015e741828ab000a', '壮壮2', '1', '1', '0', null, null, null, null, null, null, null, '2017-09-12 11:19:35', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '2017-09-12 11:19:35', '00000000-0000-0000-0000-000000000000', null, 'a585160ac656a69dcbd97088d7b68edf');
INSERT INTO `DEV_ORGANIZE` VALUES ('ff8080815e7080fd015e7419dae8000c', '部门4A', '1', '1', '1', null, null, null, null, null, null, null, '2017-09-12 12:54:58', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '2017-09-12 11:19:35', '00000000-0000-0000-0000-000000000000', null, 'a585160ac656a69dcbd97088d7b68ed2');
INSERT INTO `DEV_ORGANIZE` VALUES ('ff808081647ce47201647d203ffe0f9c', '河南省农业科学院现代农业示范区', '1', '1', '0', null, null, null, null, null, null, null, '2018-07-09 11:40:23', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '2018-07-10 09:04:40', '00000000-0000-0000-0000-000000000000', null, '402805815dfe7ea9015dfed4900f0000');
INSERT INTO `DEV_ORGANIZE` VALUES ('ff808081647ce47201647d212b480fda', '湖北省农业科学院现代农业科技示范区', '1', '1', '0', null, null, null, null, null, null, null, '2018-07-09 11:41:23', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '2018-07-09 11:45:46', '00000000-0000-0000-0000-000000000000', null, '402805815dfe7ea9015dfed4900f0000');
（3）编写并测试 向上递归查询函数 和 向下递归查询函数
编写 getChildrenOrg 函数
-
CREATE DEFINER=`root`@`%` FUNCTION `getChildrenOrg`(orgid varchar(4000)) RETURNS varchar(4000) CHARSET utf8
-
BEGIN
-
DECLARE oTemp VARCHAR(4000);
-
DECLARE oTempChild VARCHAR(4000);
-
-
SET oTemp = '';
-
SET oTempChild = CAST(orgid AS CHAR);
-
-
WHILE oTempChild IS NOT NULL
-
DO
-
SET oTemp = CONCAT(oTemp,',',oTempChild);
-
SELECT GROUP_CONCAT(ID) INTO oTempChild FROM DEV_ORGANIZE WHERE FIND_IN_SET(PARENTID,oTempChild) > 0;
-
END WHILE;
-
RETURN oTemp;
-
END
测试 函数 调用
SELECT
*
FROM
DEV_ORGANIZE
WHERE
FIND_IN_SET(ID,getChildrenOrg('402805815dfe7ea9015dfed4900f0000'))
编写 getParentOrg 函数
-
CREATE DEFINER=`root`@`%` FUNCTION `getParentOrg`(orgId VARCHAR(4000)) RETURNS varchar(4000) CHARSET utf8
-
BEGIN
-
DECLARE sTemp VARCHAR(4000);
-
DECLARE sTempChd VARCHAR(4000);
-
-
SET sTemp='$';
-
SET sTempChd = CAST(orgId AS CHAR);
-
SET sTemp = CONCAT(sTemp,',',sTempChd);
-
-
SELECT PARENTID INTO sTempChd FROM DEV_ORGANIZE WHERE ID = sTempChd;
-
WHILE sTempChd <> '' DO
-
SET sTemp = CONCAT(sTemp,',',sTempChd);
-
SELECT PARENTID INTO sTempChd FROM DEV_ORGANIZE WHERE ID = sTempChd;
-
END WHILE;
-
RETURN sTemp;
-
END
SELECT
*
FROM
DEV_ORGANIZE
WHERE
FIND_IN_SET(ID,getParentOrg('402805815dfe7ea9015dfed4900f0000'))
（4）在java 代码中 调用 编写 的 递归查询函数
-
/**
-
* mysql 版本向下递归 查询当前组织及其子组织
-
*/
-
@Override
-
public String getSqlWhereByOrgidMysql(String orgId) {
-
// TODO Auto-generated method stub
-
String sql = "SELECT * FROM DEV_ORGANIZE WHERE \r\n" +
-
"FIND_IN_SET(ID,getChildrenOrg('"+orgId+"'))";
-
String orgIds = getBySql(sql).stream().map(x->"'"+x.getId()+"'").collect(Collectors.joining(","));
-
return "ORGID IN ("+orgIds+")";
-
}
-
-
/**
-
* mysql 版本向上递归 查询当前组织及其子组织
-
*/
-
@Override
-
public String getSqlWhereByChildOrgidMysql(String orgId) {
-
String sql = "SELECT * FROM DEV_ORGANIZE WHERE \r\n" +
-
"FIND_IN_SET(ID,getParentOrg('"+orgId+"'))";
-
String orgIds = getBySql(sql).stream().map(x->"'"+x.getId()+"'").collect(Collectors.joining(","));
-
return "ORGID IN ("+orgIds+")";
-
}
文章来源: blog.csdn.net，作者：血煞风雨城2018，版权归原作者所有，如需转载，请联系作者。
原文链接：blog.csdn.net/qq_31905135/article/details/87622709
- 点赞
- 收藏
- 关注作者
评论（0）