delimiter $$  
    drop function if exists get_child_categoryt$$
    create function get_child_categoryt(in_id varchar(10)) returns varchar(1000)
    begin  
     declare ids varchar(1000) default '';
     declare tempids varchar(1000);
 
     set tempids = in_id;
     while tempids is not null do
      set ids = CONCAT_WS(',',ids,tempids);  
      select GROUP_CONCAT(id) into tempids from tbl_category where FIND_IN_SET(pid,tempids)>0;
     end while;
     return ids;
    end
    $$
    delimiter ;
 
    select get_child_categoryt(1003)
 
    SELECT * FROM tbl_category WHERE FIND_IN_SET(id, get_child_list(1003))

    delimiter $$  
    drop function if exists get_child_categoryt_test$$  
    create function get_child_categoryt_test(in_id varchar(10)) returns varchar(1000)  
    begin  
     declare ids varchar(1000) default '';  
     declare tempids varchar(1000);  
 
     set tempids = in_id;  
     while tempids is not null do  
      set ids = CONCAT_WS(',',ids,tempids);  
      select GROUP_CONCAT(pid) into tempids from tbl_category where FIND_IN_SET(id,tempids)>0;   
     end while;  
     return ids;  
    end   
    $$  
    delimiter ;  
 
    select get_child_categoryt_test(1003)
 
    SELECT * FROM tbl_category WHERE FIND_IN_SET(id, get_child_categoryt_test(1003))

    SELECT
    	t2.id 
    FROM
    	(
    	SELECT
    		@r AS _id,
    		( SELECT @r := pid FROM tbl_category WHERE id = _id ) AS parent_id,
    		@s := @s + 1 AS sort 
    	FROM
    		( SELECT @r := 1003, @s := 0 ) temp,
    		tbl_category 
    	WHERE
    		@r > 0 
    	) t1
    	JOIN tbl_category t2 ON t1._id = t2.id 
    ORDER BY
    	t1.sort DESC

 
DROP TABLE IF EXISTS `tbl_category`; 
CREATE TABLE `tbl_category`  ( 
  `id` bigint(20)  NOT NULL, 
  `name` varchar(255)  NOT NULL, 
  `pid` bigint(20)  NOT NULL, 
  PRIMARY KEY (`id`)
)
 
INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1000', '总公司', 0); 
INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1001', '北京分公司', '1000'); 
INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1002', '上海分公司', '1000'); 
INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1003', '北京研发部', '1001'); 
INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1004', '北京财务部', '1001'); 
INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1005', '北京市场部', '1001'); 
INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1006', '北京研发一部', '1003'); 
INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1007', '北京研发二部', '1003'); 
INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1008', '北京研发一部一小组', '1006'); 
INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1009', '北京研发一部二小组', '1006'); 
INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1010', '北京研发二部一小组', '1007'); 
INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1011', '北京研发二部二小组', '1007'); 
INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1012', '北京市场一部', '1005'); 
INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1013', '上海研发部', '1002'); 
INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1014', '上海研发一部', '1013'); 
INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1015', '上海研发二部', '1013'); 
 
SELECT * FROM tbl_category WHERE FIND_IN_SET(id, get_child_categoryt$$(1003))

SELECT au.id 
FROM (SELECT * FROM tbl_corp_dept WHERE parent_id IS NOT NULL) au,
     (SELECT @pid := 10) pd 
WHERE FIND_IN_SET(parent_id, @pid) > 0 
  AND @pid := concat(@pid, ',', id) 
union select id from tbl_corp_dept where FIND_IN_SET(id,@pid) > 0; --- 如果不加这行则不包括自身