MySQL树形结构的数据库表设计和查询

 1 方案一、(Adjacency List)只存储当前节点的父节点信息。
 2 -- 2018-8-11 MySQL树结构 --
 3 -- Author: xielong  Email:cnxielong@gmail.com --
 4 
 5  -- 建表 --
 6  DROP TABLE  IF EXISTS Employees;
 7 
 8  CREATE TABLE IF NOT EXISTS Employees (
 9   id INT AUTO_INCREMENT,
10   ename VARCHAR (100),
11   job VARCHAR (100),
12   parent_id INT,
13   PRIMARY KEY(id)
14 ) ENGINE = INNODB DEFAULT CHARSET = UTF8;
15 
16 
17 DESCRIBE Employees
18 
19 
20 -- 插入数据 --
21 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES(‘老王‘,‘高管‘,‘0‘);
22 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES(‘老宋‘,‘产品部主管‘,‘1‘);
23 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES(‘老牛‘,‘高管‘,‘1‘);
24 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES(‘小吴‘,‘高管‘,‘2‘);
25 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES(‘小李‘,‘高管‘,‘2‘);
26 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES(‘小欢‘,‘高管‘,‘3‘);
27 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES(‘小小‘,‘高管‘,‘3‘);
28 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES(‘小天‘,‘高管‘,‘4‘);
29 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES(‘肖丽‘,‘高管‘,‘4‘);
30 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES(‘十号‘,‘高管‘,‘5‘);
31 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES(‘十一号‘,‘高管‘,‘5‘);
32 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES(‘十二号‘,‘高管‘,‘6‘);
33 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES(‘十三号‘,‘高管‘,‘6‘);
34 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES(‘十四号‘,‘高管‘,‘7‘);
35 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES(‘小黑十五‘,‘高管‘,‘7‘);

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

 1 --  1、查询小天的所有上级 --
 2 
 3 -- 删除 --
 4    DROP FUNCTION IF EXISTS getSuperiors; 
 5 
 6 -- 创建 -- 
 7 DELIMITER $$
 8 
 9 CREATE DEFINER=`root`@`localhost` FUNCTION `getSuperiors` (`uid` INT) RETURNS VARCHAR(1000) 
10 BEGIN
11     DECLARE superiors VARCHAR(1000) DEFAULT ‘‘;
12     DECLARE sTemp INTEGER DEFAULT uid;
13     DECLARE tmpName VARCHAR(20);
14 
15     WHILE (sTemp>0) DO
16         SELECT parent_id INTO sTemp FROM employees WHERE id = sTemp;
17         SELECT ename INTO tmpName FROM employees WHERE id = sTemp;
18         IF(sTemp>0)THEN
19             SET superiors = CONCAT(tmpName,‘,‘,superiors);
20         END IF;
21     END WHILE;
22         SET superiors = LEFT(superiors,CHARACTER_LENGTH(superiors)-1);
23     RETURN superiors;
24 END $$
25 
26 -- 调用 --
27 SELECT   getSuperiors(8) 上司;

 1   --  查询老王管理的所有员工 --
 2 
 3 -- 删除 --
 4    DROP PROCEDURE IF EXISTS getSubordinate 
 5 
 6 -- 创建 -- 
 7    DELIMITER $$
 8 
 9 CREATE DEFINER = `root` @`localhost` FUNCTION `getSubordinate` (`uid` INT) RETURNS VARCHAR (2000)
10 BEGIN
11   DECLARE str VARCHAR (1000);
12   DECLARE cid VARCHAR (100);
13   DECLARE result VARCHAR (1000);
14   DECLARE tmpName VARCHAR (100);
15   SET str = ‘$‘;
16   SET cid = CAST(uid AS CHAR(10));
17   WHILE
18     cid IS NOT NULL DO 
19     SET str = CONCAT(str, ‘,‘, cid);
20     SELECT  GROUP_CONCAT(id) INTO cid  FROM  employees  WHERE FIND_IN_SET(parent_id, cid);
21   END WHILE;
22   SELECT
23     GROUP_CONCAT(ename) INTO result   FROM  employees  WHERE FIND_IN_SET(parent_id, str);
24   RETURN result;
25 END $$
26 
27 -- 调用 --
28 SELECT   getSubordinate(1)