使用sql查询部门表中某一个部门的下级部门,部门表是树结构有pid；

背景:

在工作中经常需要使用到树结构,一般情况是将数据查到内存中,整理数据结构后使用后端代码进行组装树,现在如果让你用sql代码来进行查找某一个部门下的子部门有哪些,你又该如何应对呢.

今天分享关于这个问题的思考

表结构:

CREATE TABLE `xc_department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL DEFAULT '0' COMMENT '父节点ID  0代表公司',
  `department_name` varchar(255) DEFAULT NULL COMMENT '部门名称',
  `path` varchar(255) DEFAULT NULL COMMENT '路径',
  `direct_leader_uid` int(11) DEFAULT NULL COMMENT '直属领导uid',
  `direct_leader_name` varchar(24) DEFAULT NULL COMMENT '直属领导名字',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COMMENT='部门组织表';

需求:给出一个部门的id,查找这个部门(包括这个部门)的所有下级部门的id.例如某个部门id为2,下级部门有3,4,5,6,7,则查找结果应该是:2,3,4,5,6,7.这个需求的作用是统计该部门下的所有的用户数量.

写法1:

SELECT
    t3.id
        FROM
            (
                SELECT
                    t1.*,
                    IF
                        (
                                find_in_set( t1.pid, @pids ) > 0,
                                @pids := concat( @pids, ',', t1.id ),
                                0
                        ) AS ischild
                FROM
                        ( SELECT * FROM xc_department ) t1,
                        ( SELECT @pids := 2) t2
            ) t3
        WHERE
            ischild != 0
	OR t3.id = 2;
	

解读:

这段 SQL 查询中的 IF 语句用于在查询结果中创建一个名为 ischild 的列，其值取决于条件的成立与否。让我来解释其中的部分：

1. `IF(condition, value_if_true, value_if_false)`: 这是 MySQL 中的条件语句，它根据 condition 的真假返回不同的值。在这个查询中，condition 是 `find_in_set( t1.pid, @pids ) > 0`，value_if_true 是 `@pids := concat( @pids, ',', t1.id )`，而 value_if_false 是 0。

2. `find_in_set( t1.pid, @pids )`: 这是 MySQL 中的函数，用于在逗号分隔的字符串中查找某个值。在这里，它的作用是检查变量 @pids 中是否包含 t1.pid 的值。

3. `@pids := concat( @pids, ',', t1.id )`: 这部分是赋值操作，它的作用是将 t1.id 的值追加到变量 @pids 的末尾，以逗号分隔不同的部门ID。

4. `0`: 这是在条件不成立时要返回的值。

因此，整个 IF 语句的作用是：如果 t1.pid 的值在变量 @pids 中存在，就将 t1.id 的值追加到 @pids 的末尾；否则返回 0。这个逻辑似乎是在处理部门之间的父子关系路径，以便在查询结果中标识出哪些部门是子部门。

写法2:

	SELECT  
    t1.id  
FROM  
    xc_department t1  
JOIN  
    (SELECT @pids := 2) t2  
JOIN  
    (SELECT   
        t1.*,  
        IF(  
            find_in_set(t1.pid, @pids) > 0,  
            @pids := concat(@pids, ',', t1.id),  
            0  
        ) AS ischild  
    FROM   
        xc_department t1  
    ) t3 ON t3.id = t1.id  
WHERE  
    ischild != 0 OR t1.id = 2;  

解读;

这个 SQL 查询的目的是从名为 `xc_department` 的表中选择部门的 ID，并标识出哪些部门是子部门。以下是对这个查询的解读：

1. 初始化变量：首先，它初始化了一个用户变量 @pids，并将其设为 2。

2. 创建临时表：它创建了一个临时表 t3，其中包含了 xc_department 表中的所有列，并增加了一个名为 ischild 的列。ischild 列的值取决于是否满足了条件。如果 t1.pid 的值在变量 @pids 中存在，则将 t1.id 的值追加到 @pids 的末尾；否则返回 0。

3. 连接表：使用了JOIN语法将 t1 表与 t3 表连接，条件是 t3.id = t1.id。这确保了只有那些具有子部门或者id等于2的部门被选择出来。

4. 选择结果：最终查询选择了 t1 表中的 id 列，同时过滤出 ischild 不等于 0 的行，以及 id 等于 2 的行。

总体来说，这个查询的目的是选择部门的 ID，并且标识出哪些部门是子部门，以便在查询结果中进行进一步的处理或展示。

最后从查询结果也能看到两者的查询结果是一致的,也就是两个sql是等价的,有任何疑问可以评论区讨论或者私信我