MYSQL循环查询上级部门

最新推荐文章于 2024-04-22 17:11:06 发布

胸中有沟壑

最新推荐文章于 2024-04-22 17:11:06 发布

阅读量252

点赞数 2

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/weixin_44419615/article/details/137971579

版权

创建表

CREATE TABLE `ceshi_dept` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `deptname` varchar(255) DEFAULT NULL,
  `deptnum` int DEFAULT NULL,
  `parentnum` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

添加数据

INSERT INTO `ruoyi`.`ceshi_dept`(`id`, `deptname`, `deptnum`, `parentnum`) VALUES (1, '董事会', 1, NULL);
INSERT INTO `ruoyi`.`ceshi_dept`(`id`, `deptname`, `deptnum`, `parentnum`) VALUES (2, '生产研发部', 2, 1);
INSERT INTO `ruoyi`.`ceshi_dept`(`id`, `deptname`, `deptnum`, `parentnum`) VALUES (3, '软件部', 3, 2);
INSERT INTO `ruoyi`.`ceshi_dept`(`id`, `deptname`, `deptnum`, `parentnum`) VALUES (4, '测试部', 4, 3);

查询所有上级部门

WITH RECURSIVE dept_hierarchy AS (  
    SELECT id, deptname, deptnum, parentnum  
    FROM ceshi_dept  
    WHERE id = 4  -- 开始查询的起点  
    UNION ALL  
    SELECT c.id, c.deptname, c.deptnum, c.parentnum  
    FROM ceshi_dept c  
    JOIN dept_hierarchy dh ON c.id = dh.parentnum  
) 
SELECT * FROM dept_hierarchy where id!=4  ORDER BY id asc;  -- 排除起始点本身

查询所有下级部门

WITH RECURSIVE dept_hierarchy AS (  
    SELECT id, deptname, deptnum, parentnum  
    FROM ceshi_dept  
    WHERE parentnum = 1  -- 开始查询的上级部门ID  
    UNION ALL  
    SELECT c.id, c.deptname, c.deptnum, c.parentnum  
    FROM ceshi_dept c  
    JOIN dept_hierarchy dh ON c.parentnum = dh.id  
)  
SELECT * FROM dept_hierarchy ORDER BY id asc;