MySQL之递归查询

分类递归查询子节点

    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))

分类递归查询父级节点,SQL1

    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))

分类递归查询父级节点,SQL2

    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

select GROUP_CONCAT(pid) into tempids from tbl_category where FIND_IN_SET(id,tempids)>0;递归查询子节点还是父节点关键语句

• 查询子节点select GROUP_CONCAT(id) into tempids from tbl_category where FIND_IN_SET(pid,tempids)>0;
• 查询父节点select GROUP_CONCAT(pid) into tempids from tbl_category where FIND_IN_SET(id,tempids)>0;

例子sql

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; --- 如果不加这行则不包括自身