Mysql查找部门的下属部门(包括下属部门的下属部门)

现有部门表department,要求给出部门Id能够查出下属部门及下属部门的信息,department表有字段id,parent_id,department_name,level四个字段,现给出sql:

SELECT d2.* 
	FROM (
         SELECT t1.id,
                 IF(find_in_set(t1.parent_id, @pids) > 0, @pids := concat(@pids, ',', t1.id), 0) AS leafNodeId
         FROM (SELECT d1.id, d1.parent_id FROM department d1) t1,
		  (SELECT @pids := #{***给定部门id***} ) t2 
         ) t3 
         INNER JOIN department d2 ON d2.id = t3.id
WHERE t3.leafNodeId != 0

此sql只查询给定部门Id下的部门,不包含本部门,比如我查询id为3的部门,查询sql如下:

查询结果如下图所示:

下面详细解析sql:
“SELECT d1.id, d1.parent_id FROM department d1” 查询表中所有记录;
“SELECT @pids := 3” 指定要查询的Id为3;
“IF(find_in_set(t1.parent_id, @pids) > 0, @pids := concat(@pids, ‘,’, t1.id), 0) AS leafNodeId” 如果表中有记录的parent_id在 @pids中的话(即find_in_set(t1.parent_id, @pids) > 0),那么就将该记录的id拼接到@pids,leafNodeId为拼接后的@pids,否则leafNodeId为0,也就是说如果t1中有记录的id在@pids中那么下一次也将此id作为parent_id进行查找,如果不在@pids中则leafNodeId为0;
"WHERE t3.leafNodeId != 0"就是只查找该id下面及下面的下面部门;
"INNER JOIN department d2 ON d2.id = t3.id"只是为了更好的看查询结果