1、邻接表(Adjacency List)

  • 实例:现在有一个要存储一下公司的人员结构,大致层次结构如下:

1.png

  • 那么怎么存储这个结构?并且要获取以下信息:

    • 1.查询小天的直接上司。
    • 2.查询老宋管理下的直属员工。
    • 3.查询小天的所有上司。
    • 4.查询老王管理的所有员工。
方案一、(Adjacency List)只存储当前节点的父节点信息。


  -- 2018-8-11 MySQL树结构 --
    -- Author: xielong  Email:cnxielong@gmail.com --
    
     -- 建表 --
     DROP TABLE  IF EXISTS Employees;
    
     CREATE TABLE IF NOT EXISTS Employees (
      id INT AUTO_INCREMENT,
      ename VARCHAR (100),
      job VARCHAR (100),
      parent_id INT,
      PRIMARY KEY(id)
    ) ENGINE = INNODB DEFAULT CHARSET = UTF8;
    
    
    DESCRIBE Employees
    
    
    -- 插入数据 --
    INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('老王','高管','0');
    INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('老宋','产品部主管','1');
    INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('老牛','高管','1');
    INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('小吴','高管','2');
    INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('小李','高管','2');
    INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('小欢','高管','3');
    INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('小小','高管','3');
    INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('小天','高管','4');
    INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('肖丽','高管','4');
    INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('十号','高管','5');
    INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('十一号','高管','5');
    INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('十二号','高管','6');
    INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('十三号','高管','6');
    INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('十四号','高管','7');
    INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('小黑十五','高管','7');
    
  • 数据库结构信息:

2.png

  • 好的,现在开始进入回答环节:
  • 1.查询小天的直接上司:
 -- 查询小天的上级Id  隐式内连接 关键条件父节点 --
 SELECT e2.id, e2.ename, e2.job FROM Employees e1, Employees e2 WHERE e1.`parent_id` = e2.id AND e1.id=8
 
 -- 查询小天的上级Id  显示内连接 关键条件父节点 --
 SELECT e2.id, e2.ename, e2.job FROM Employees e1 INNER JOIN Employees e2 WHERE e1.`parent_id` = e2.id AND e1.id=8

3.png

  • 2.查询老宋管理下的直属员工:
    -- 查询老宋管理下的直属员工:隐式内连接 关键条件父子节点 老宋ID=2 --
    SELECT e2.id, e2.ename, e2.job FROM Employees e1, Employees e2 WHERE e1.id=2 AND e2.`parent_id` = e1.id
    
    -- 查询小天的上级Id  显示内连接 关键条件父子节点 老宋.ename='老宋' --
    SELECT e2.id, e2.ename, e2.job FROM Employees e1 INNER JOIN Employees e2 WHERE e2.`parent_id` = e1.id AND e1.ename='老宋'

4.png

  • 3.查询小天的所有上司。

    • 这里肯定没法直接查,只能用循环进行循环查询,先查直接上司,再查直接上司的直接上司,依次循环,这样麻烦的事情,还是得先建立一个存储过程:
    • 睁大眼睛看仔细了,接下来是骚操作环节:
    --  1、查询小天的所有上级 --
    
    -- 删除 --
       DROP FUNCTION IF EXISTS getSuperiors; 
    
    -- 创建 -- 
    DELIMITER $$
    
    CREATE DEFINER=`root`@`localhost` FUNCTION `getSuperiors` (`uid` INT) RETURNS VARCHAR(1000) 
    BEGIN
        DECLARE superiors VARCHAR(1000) DEFAULT '';
        DECLARE sTemp INTEGER DEFAULT uid;
        DECLARE tmpName VARCHAR(20);
    
        WHILE (sTemp>0) DO
            SELECT parent_id INTO sTemp FROM employees WHERE id = sTemp;
            SELECT ename INTO tmpName FROM employees WHERE id = sTemp;
            IF(sTemp>0)THEN
                SET superiors = CONCAT(tmpName,',',superiors);
            END IF;
        END WHILE;
            SET superiors = LEFT(superiors,CHARACTER_LENGTH(superiors)-1);
        RETURN superiors;
    END $$
    
    -- 调用 --
    SELECT   getSuperiors(8) 上司;
  • 这一段存储过程可以查询子节点的所有父节点,来试验一下 
  • 好的,骚操作完成。
  • 显然,这样。获取子节点的全部父节点的时候很麻烦。。

5.png

  • 4.查询老王管理的所有员工。

思路如下:先获取所有父节点为老王id的员工id,然后将员工姓名加入结果列表里,在调用一个神奇的查找函数,即可进行神奇的查找:


    --  查询老王管理的所有员工 --
    
    -- 删除 --
       DROP PROCEDURE IF EXISTS getSubordinate 
    
    -- 创建 -- 
       DELIMITER $$
    
    CREATE DEFINER = `root` @`localhost` FUNCTION `getSubordinate` (`uid` INT) RETURNS VARCHAR (2000)
    BEGIN
      DECLARE str VARCHAR (1000);
      DECLARE cid VARCHAR (100);
      DECLARE result VARCHAR (1000);
      DECLARE tmpName VARCHAR (100);
      SET str = '$';
      SET cid = CAST(uid AS CHAR(10));
      WHILE
        cid IS NOT NULL DO 
        SET str = CONCAT(str, ',', cid);
        SELECT  GROUP_CONCAT(id) INTO cid  FROM  employees  WHERE FIND_IN_SET(parent_id, cid);
      END WHILE;
      SELECT
        GROUP_CONCAT(ename) INTO result   FROM  employees  WHERE FIND_IN_SET(parent_id, str);
      RETURN result;
    END $$
    
    -- 调用 --
    SELECT   getSubordinate(1)
  • 看神奇的结果:

6.png

  • 虽然搞出来了,但说实话,真是不容易。。。
  • 这种方法的优点是存储的信息少,查直接上司和直接下属的时候很方便,缺点是多级查询的时候很费劲。所以当只需要用到直接上下级关系的时候,用这种方法还是不错的,可以节省很多空间。

2、继承关系驱动的设计表和基于左右值编码的设计

参考链接: https://blog.csdn.net/lj1314ailj/article/details/52074216

参考:MySQL 实现树形的遍历

  • MySQL 实现树形的遍历(关于多级菜单栏以及多级上下部门的查询问题)

参考链接: https://blog.csdn.net/mchdba/article/details/39277301


作者:X-Dragon烟雨任平生
来源:CSDN
原文:https://blog.csdn.net/xielong0509/article/details/81592934

最后修改:2019 年 04 月 17 日 11 : 27 PM
如果觉得我的文章对你有用,请随意赞赏