sql两种递归

jiang2360

已于 2023-07-25 00:01:09 修改

阅读量52

点赞数

分类专栏： MySQL 文章标签： sql 数据库

于 2023-07-24 23:12:15 首次发布

本文链接：https://blog.csdn.net/jiang2360/article/details/131906675

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

废话少说，直接上代码

MySQL8版本以下的

CREATE TABLE t_areainfo (
id int(11) NOT null 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;

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `pid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;



INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1000', '总公司', NULL);
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1001', '北京分公司', '1000');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1002', '上海分公司', '1000');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1003', '北京研发部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1004', '北京财务部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1005', '北京市场部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1006', '北京研发一部', '1003');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1007', '北京研发二部', '1003');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1008', '北京研发一部一小组', '1006');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1009', '北京研发一部二小组', '1006');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1010', '北京研发二部一小组', '1007');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1011', '北京研发二部二小组', '1007');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1012', '北京市场一部', '1005');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1013', '上海研发部', '1002');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1014', '上海研发一部', '1013');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1015', '上海研发二部', '1013');

INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (1, 0, '中国', 0, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (2, 0, '华北区', 1, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (3, 0, '华南区', 1, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (4, 0, '北京', 2, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (5, 0, '海淀区', 4, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (6, 0, '丰台区', 4, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (7, 0, '朝阳区', 4, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (8, 0, '大兴区', 4, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (9, 0, '东城区', 4, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (10, 0, '西城区', 4, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (11, 0, '崇文区', 4, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (12, 0, '宣武区', 4, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (13, 0, '石景山区', 4, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (14, 0, '门头沟区', 4, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (15, 0, '房山区', 4, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (16, 0, '通州区', 4, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (17, 0, '顺义区', 4, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (18, 0, '昌平区', 4, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (19, 0, '怀柔区', 4, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (20, 0, '平谷区', 4, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (46, 0, '吉林省', 1, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (47, 0, '黑龙江', 46, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (48, 0, '哈尔滨', 46, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (49, 0, '大连', 46, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (50, 0, '沈阳', 46, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (63, 0, '松原', 46, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (64, 0, '吉林市', 46, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (65, 0, '葫芦岛', 46, 0);
INSERT INTO t_areainfo (id, level, name, parentId, status) VALUES (66, 0, '扶余', 46, 0);

DROP FUNCTION IF EXISTS get_child_list;

DELIMITER //
CREATE FUNCTION get_child_list(in_id varchar(10)) RETURNS varchar(1000)
DETERMINISTIC
BEGIN
    DECLARE ids varchar(1000) DEFAULT '';
    DECLARE tempids varchar(1000);

    SET tempids = in_id;
    WHILE tempids IS NOT NULL DO
        SET ids = CONCAT_WS(',',ids,tempids);
        SELECT GROUP_CONCAT(id) INTO tempids FROM dept WHERE FIND_IN_SET(pid,tempids)>0;
    END WHILE;
    RETURN ids;
END//
DELIMITER ;

ALTER TABLE dept MODIFY COLUMN pid VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


SELECT * FROM dept WHERE FIND_IN_SET(id, get_child_list('1003'));


-----------------------------------------------------------------------------


DROP FUNCTION IF EXISTS get_child_list;

DELIMITER //
CREATE FUNCTION get_child_list(in_id varchar(10)) RETURNS varchar(1000)
DETERMINISTIC
BEGIN
    DECLARE ids varchar(1000) DEFAULT '';
    DECLARE tempids varchar(1000);

    SET tempids = in_id;
    WHILE tempids IS NOT NULL DO
        SET ids = CONCAT_WS(',',ids,tempids);
        SELECT GROUP_CONCAT(id) INTO tempids FROM t_areainfo WHERE FIND_IN_SET(parentId,tempids)>0;
    END WHILE;
    RETURN ids;
END//
DELIMITER ;
SELECT * FROM t_areainfo WHERE FIND_IN_SET(id, get_child_list('46'));

------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS get_child_list;
DELIMITER //
CREATE FUNCTION get_child_list(in_id varchar(1000)) RETURNS varchar(1000)
DETERMINISTIC
BEGIN
    DECLARE ids varchar(1000) DEFAULT '';
    DECLARE tempids varchar(1000);

    SET tempids = in_id;
    WHILE tempids IS NOT NULL DO
        SET ids = CONCAT_WS(',',ids,tempids);
        SELECT GROUP_CONCAT(id) INTO tempids FROM t_privilege WHERE FIND_IN_SET(parent_key,tempids)>0;
    END WHILE;
    RETURN ids;
END//
DELIMITER ;

-- 查子级
SELECT * FROM t_privilege WHERE FIND_IN_SET(id, get_child_list('ThreeRouter'));

SELECT 
    mr.id,
    mr.`key`,
    mr.`name`,
    mr.parent_key,
    mr.type,
  
    r.`role_name` AS 角色名称,
    r.`status` AS 状态
FROM 
    (SELECT p.id,p.type,p.`name`,p.`key`,p.parent_key FROM t_privilege p WHERE FIND_IN_SET(id, get_child_list('ThreeRouter')))
		AS mr
LEFT JOIN 
    t_role_privilege rp ON mr.parent_key = rp.privilege_key
LEFT JOIN 
    t_role r ON rp.role_id = r.id;

MySQL8版本以上的

CREATE TABLE `t_privilege` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '功能权限表主键id',
  `type` tinyint(4) NOT NULL COMMENT '1.菜单 2.功能点',
  `name` varchar(50) NOT NULL COMMENT '菜单名称',
  `key` varchar(1000) NOT NULL COMMENT '路由name 英文关键字',
  `url` text COMMENT '路由path/type=3为API接口',
  `sort` int(11) NOT NULL DEFAULT '0' COMMENT '排序',
  `parent_key` varchar(1000) DEFAULT NULL COMMENT '父级key',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `key` (`key`) USING BTREE,
  KEY `type` (`type`) USING BTREE,
  KEY `parent_key` (`parent_key`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=179 DEFAULT CHARSET=utf8 COMMENT='权限功能表';


CREATE TABLE `t_role_privilege` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NOT NULL COMMENT '角色id',
  `privilege_key` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '权限key',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13198 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色权限功能表';


CREATE TABLE `t_role` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `role_name` varchar(20) NOT NULL COMMENT '角色名称',
  `remark` varchar(255) DEFAULT NULL COMMENT '角色描述',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `status` int(11) NOT NULL DEFAULT '1' COMMENT '1启用 2禁用',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8 COMMENT='角色表';

查询

RECURSIVE 关键字：递归

WITH RECURSIVE MenuCTE AS (
  SELECT *
  FROM t_privilege
  WHERE `key` = 'ThreeRouter'  
  UNION ALL
  SELECT m.*
  FROM t_privilege m
  JOIN MenuCTE cte ON m.parent_key = cte.`key` --
)
 -- SELECT * FROM MenuCTE;

SELECT
  rp.role_id,
  rp.privilege_key,
  r.role_name,
  p.name AS privilege_name
FROM t_role_privilege AS rp
JOIN t_role AS r ON rp.role_id = r.id
JOIN (
  SELECT DISTINCT `key`, name FROM MenuCTE
) AS p ON rp.privilege_key = p.`key`;