初始化脚本

CREATE TABLE t_depart (
id int,
pid int,
name VARCHAR(100)
);

insert INTO t_depart VALUES(1, NULL, '1级');
insert INTO t_depart VALUES(2, 1, '1.1级');
insert INTO t_depart VALUES(3, 1, '1.2级');
insert INTO t_depart VALUES(4, 2, '1.1.1级');
insert INTO t_depart VALUES(5, NULL, '2级');

查所有数据

select * from t_depart;

MySQL 8.0 版

1、查当前和所有子级

WITH RECURSIVE r AS (
	SELECT id, name, pid FROM t_depart where id = 1
	union
	SELECT xzqh_.id, xzqh_.name, xzqh_.pid FROM t_depart xzqh_  INNER JOIN r 
	on r.id = xzqh_.pid -- 查当前和所有子级
	)
SELECT id, name, pid FROM r;

2、查当前和所有父级

WITH RECURSIVE r AS (
	SELECT id, name, pid FROM t_depart where id = 4
	union
	SELECT xzqh_.id, xzqh_.name, xzqh_.pid FROM t_depart xzqh_  INNER JOIN r 
	on r.pid = xzqh_.id -- 查当前和所有父级
	)
SELECT id, name, pid FROM r;

5.7 版

1、查当前和所有子级

存储过程+中间表的方式实现，sql 脚本改自

https://www.jianshu.com/p/9e7c2a481e49

delimiter $$
CREATE PROCEDURE `findOrgChildList`(IN deptId int)
BEGIN
  DECLARE v_org int DEFAULT null;
  DECLARE done INTEGER DEFAULT 0;
    -- 查询结果放入游标中
  DECLARE C_org CURSOR FOR SELECT d.id
                           FROM t_depart d
                           WHERE d.pid = deptId;
  DECLARE CONTINUE HANDLER FOR NOT found SET done=1;
  SET @@max_sp_recursion_depth = 10;
    
    -- 传入的组织id写入临时表
 INSERT INTO tmp_org VALUES (deptId);
  OPEN C_org;
  FETCH C_org INTO v_org;
  WHILE (done=0)
  DO
        -- 递归调用，查找下级
    CALL findOrgChildList(v_org);
    FETCH C_org INTO v_org;
  END WHILE;
  CLOSE C_org;
END
$$


delimiter $$
CREATE PROCEDURE findOrgList(IN deptId int)
BEGIN
   DROP TEMPORARY TABLE IF EXISTS tmp_org;
    -- 创建临时表
    CREATE TEMPORARY TABLE tmp_org(org_id int);
    -- 清空临时表数据
    delete FROM tmp_org;
    -- 发起调用
    CALL findOrgChildList(deptId);
    -- 从临时表查询结果
    SELECT t_depart.* FROM tmp_org,t_depart where t_depart.id = tmp_org.org_id ORDER BY tmp_org.org_id;
    -- 删除临时表
    DROP temporary TABLE tmp_org;
END
$$

调用存储过程

CALL findOrgList(2);

2、查当前和所有父级

SELECT id,name,pid
    FROM ( 
        SELECT 
            @r AS _id, 
            (SELECT @r := pid FROM t_depart WHERE id = _id) AS parent_id
        FROM 
            (SELECT @r := 4) vars, 
            t_depart
        WHERE @r <> 0) T1 
    JOIN t_depart T2 
    ON T1._id = T2.id
ORDER BY id;