mysql树形结构的查询案例

表结构

CREATE TABLE `t_organization` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '组织id',
  `name` varchar(50) NOT NULL COMMENT '组织名称',
  `pid` int(8) DEFAULT NULL COMMENT '组织上级id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

录入数据

案例一：需要获取组织，以及根据组织的上级id获取上级组织名称

这种情况可以通过左连接实现

SELECT
    t1.id,
    t1.`name`,
    t1.pid,
    t2.`name` 
FROM
    t_organization t1
    LEFT JOIN t_organization t2 ON t1.pid = t2.id

可以看到左连接是以左表为基准，通过关联关系id = pid去找到对应的上级组织记录，所以空的id找不到对应的记录，返回空

案例二：需要获取组织，以及根据组织的完整路径

有时候我们需要获取某个组织的完整路径 如
部门C/部门C_2/部门C_2_1/部门C_2_1_1
编写存储过程，生成一个临时表tmpLst，按照层级把每一条记录插入到临时表，然后每次从临时表查当前层级的组织，循环去查组织表的上级组织，直到结果ROW_COUNT = 0为止，代表当前层级下的所有组织已经是最后一级

CREATE PROCEDURE proce ( IN rootid INTEGER ) 

BEGIN
    DECLARE LEVEL INT;
    DROP TABLE  IF  EXISTS tmpLst;
    CREATE TABLE tmpLst ( 
        id INT, 
        nLevel INT, 
        orgName VARCHAR ( 8000 ) 
    );
    
    SET LEVEL = 0;
    INSERT INTO tmpLst 
    SELECT  
        id, 
        LEVEL,
        `name`
        FROM
        t_organization;     
    WHILE
            ROW_COUNT()> 0 
    DO
            
        SET LEVEL = LEVEL + 1;
        INSERT INTO tmpLst SELECT
        A.ID,
        LEVEL,
        concat( B.orgName, '/' ,A.name ) 
        FROM
            t_organization A,
            tmpLst B 
        WHERE
            A.PID = B.ID 
            AND B.nLevel = LEVEL - 1;
    END WHILE;
    
END;

查询到的结果，大家可以自行优化一下显示方式和查询的字段

当然还有另一种方式，从设计上解决
如新加一个唯一约束，把组织的约束定义为 ORG_001_ORG_001_002_ORG_001_003 这样的形式
当需要查询ORG_001所有的下级时，只需要查询约束 like ORG_001% 即可
当需要查询ORG_001_002所有上级时，只需要查询约束 like %ORG_001_002
不过问题在于如果组织的存在架构调整，如，ORG_001_002调整到了 ORG_002下，因为树型结构变化了，直接用like无法查询到正确数据，这个时候要考虑是否允许调整或者调整后修改对应的唯一约束