Mysql example of querying all lower-level multi-level sub-departments based on a certain department ID

DROP TABLE IF EXISTS `sys_dept`;
CREATE TABLE `sys_dept` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Department number',
 `p_id` int(11) DEFAULT NULL COMMENT 'Parent department number',
 `title` varchar(255) DEFAULT NULL COMMENT 'Parent department name',
 `is_open` int(11) DEFAULT NULL COMMENT 'Whether to expand (0-expand, 1-not expand)',
 `address` varchar(255) DEFAULT NULL COMMENT 'Department address',
 `create_time` datetime DEFAULT NULL COMMENT 'Creation time',
 `remark` varchar(255) DEFAULT NULL COMMENT 'Remarks',
 PRIMARY KEY (`id`) USING BTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO sys_dept (p_id,title,is_open,address,create_time,remark) VALUES 
(0,'General Manager's Office',1,'Shenzhen','2019-04-10 14:06:32.000','Big Boss')
,(1,'Sales Department',0,'Wuhan','2019-04-10 14:06:32.000','Programmer Loser')
,(1,'Operation Department',0,'Wuhan','2019-04-10 14:06:32.000','None')
,(1,'Production Department',0,'Wuhan','2019-04-10 14:06:32.000','None')
,(2,'Sales Department 1',0,'Wuhan','2019-04-10 14:06:32.000','Sales Department 1')
,(2,'Sales Department 2',0,'Wuhan','2019-04-10 14:06:32.000','Sales Department 2')
,(2,'Sales Department 3',0,'Guangzhou','2019-04-10 14:06:32.000','Sales Department 3')
,(2,'Sales Department 4',0,'Guangzhou','2019-04-10 14:06:32.000','Sales Department 4')
,(2,'Sales Department 5',0,'Guangzhou','2019-04-10 14:06:32.000','Sales Department 5')
,(3,'Operation Department 1',0,'Wuhan','2019-04-10 14:06:32.000','Operation Department 1')
,(3,'Operation Department 2',0,'Wuhan','2019-04-10 14:06:32.000','Operation Department 2')
,(3,'Operation Department 3',0,'Wuhan','2019-04-10 14:06:32.000','Operation Department 3')
,(3,'Operation Department 4',0,'Wuhan','2019-04-10 14:06:32.000','Operation Department 4')
,(3,'Operation Department 5',0,'Wuhan','2019-04-10 14:06:32.000','Operation Department 5')
,(4,'Production Department 1',1,'Shenzhen','2019-11-23 09:50:23.000','Production Department 1')
,(4,'Production Department 2',1,'Shenzhen','2019-11-23 09:50:23.000','Production Department 2')
,(4,'Production Department 3',1,'Shenzhen','2019-11-23 09:50:23.000','Production Department 3')
,(5,'Sales of one group',1,'Shenzhen','2019-11-23 09:50:23.000','Sales of one group')
,(5,'Sales Department 1, Group 2',1,'Shenzhen','2019-11-23 09:50:23.000','Sales Department 1, Group 2')
,(5,'Sales of Unit 1 and 3',1,'Shenzhen','2019-11-23 09:50:23.000','Sales of Unit 1 and 3')
,(6,'Sales Department 2 Group 1',1,'Shenzhen','2019-11-23 09:50:23.000','Sales Department 2 Group 1')
,(6,'Sales Department 2 Group 2',1,'Shenzhen','2019-11-23 09:50:23.000','Sales Department 2 Group 2')
,(17,'Production Department 3 Group 1',1,'Shenzhen','2019-11-23 09:50:23.000','Production Department 3 Group 1')
,(17,'Production Department 3 Group 2',1,'Shenzhen','2019-11-23 09:50:23.000','Production Department 3 Group 2')
,(17,'Production Department 3 Group 3',1,'Shenzhen','2019-11-23 09:50:23.000','Production Department 3 Group 3')
;

select
 id,title
from
 (
 select
 t1.id,t1.title,
 if(find_in_set(p_id, @pids) > 0,
 @pids := concat(@pids, ',', id),
 0) as ischild
 from
 (
 select
  id,
  p_id,
  title
 from
  ssmdemo.sys_dept t
 order by
  p_id,
  id ) t1,
 (
  select @pids := 17) t2 ) t3
where
 ischild != 0