mysql查询树形结构上、下级

女爷i 2023-10-05 17:16 13阅读 0赞

## 1、存储函数 ##

> **find\_in\_set(str,strlist)** : 查询字符串str是否在strlist存在，返回存在字符串str的个数。
> 
>  *  str 要查询的字符串
>  *  strlist 字段名 参数以”,”分隔 如 (1,2,6,8)
>  *  查询字段(strlist)中包含(str)的结果，返回结果为null或记录
> 
> 假如字符串str在由N个子链组成的字符串列表strlist 中，则返回值的范围在 1 到 N 之间。 一个字符串列表就是一个由一些被 ‘,’ 符号分开的子链组成的字符串。
> 
> 如果第一个参数是一个常数字符串，而**第二个是type SET列**，则**FIND\_IN\_SET() 函数被优化，使用比特计算**。
> 
> 如果str不在strlist 或strlist 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。

> **group\_concat(**\[distinct\] 要连接的字段 \[order by 排序字段 asc/desc \] \[separator '分隔符'\] **)**
> 
> 功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
> 
> 说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

>  **ROW\_COUNT() ：** 表示数据库中数据更新的条数。

>     if（express1,express2,express3）条件语句，if语句类似三元表达式，当exprss1成立时，执行express2,否则执行express3;

用存储函数，查询所有子节点列表，并拼接成字符串返回。

**表结构、数据：**

drop table if exists org_info;
    create table org_info (
      ORG_ID varchar(60) not null,
      ORG_NAME varchar(60) not null,
      PARENT_ORG_ID varchar(60) default null,
      primary key (org_Id)
    ) engine=innodb default charset=utf8 ;
    
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100000', '测试机构100000', '0'    );
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100001', '测试机构100001', '100000');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100002', '测试机构100002', '100001');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100003', '测试机构100003', '100002');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100004', '测试机构100004', '100003');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100005', '测试机构100005', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100006', '测试机构100006', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100007', '测试机构100007', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100008', '测试机构100008', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100009', '测试机构100009', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100010', '测试机构100010', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100011', '测试机构100011', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100012', '测试机构100012', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100013', '测试机构100013', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100014', '测试机构100014', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100015', '测试机构100015', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100016', '测试机构100016', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100017', '测试机构100017', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100018', '测试机构100018', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100019', '测试机构100019', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100020', '测试机构100020', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100021', '测试机构100021', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100022', '测试机构100022', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100023', '测试机构100023', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100024', '测试机构100024', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100025', '测试机构100025', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100026', '测试机构100026', '100004');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100027', '测试机构100027', '100002');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100028', '测试机构100028', '100027');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100029', '测试机构100029', '100028');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100030', '测试机构100030', '100028');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100031', '测试机构100031', '100027');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100032', '测试机构100032', '100027');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100033', '测试机构100033', '100027');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100034', '测试机构100034', '100027');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100035', '测试机构100035', '100027');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100036', '测试机构100036', '100027');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100037', '测试机构100037', '100027');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100038', '测试机构100038', '100027');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100039', '测试机构100039', '100027');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100040', '测试机构100040', '100027');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100041', '测试机构100041', '100027');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100042', '测试机构100042', '100031');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100043', '测试机构100043', '100032');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100044', '测试机构100044', '100033');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100045', '测试机构100045', '100034');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100046', '测试机构100046', '100035');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100047', '测试机构100047', '100036');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100048', '测试机构100048', '100037');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100049', '测试机构100049', '100038');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100050', '测试机构100050', '100039');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100051', '测试机构100051', '100040');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100052', '测试机构100052', '100041');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100053', '测试机构100053', '100041');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100054', '测试机构100054', '100002');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100055', '测试机构100055', '100054');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100056', '测试机构100056', '100055');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100057', '测试机构100057', '100055');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100058', '测试机构100058', '100054');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100059', '测试机构100059', '100058');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100060', '测试机构100060', '100002');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100061', '测试机构100061', '100060');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100062', '测试机构100062', '100061');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100063', '测试机构100063', '100061');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100064', '测试机构100064', '100060');
    INSERT INTO org_info(ORG_ID, ORG_NAME, PARENT_ORG_ID) VALUES ('100065', '测试机构100065', '100064');

**查询下级：**

-- 创建存储函数
    drop function if exists getLowOrgIds;
    delimiter $$
    create function getLowOrgIds(orgId varchar(20)) returns varchar(1000)
    begin 
    	declare temp varchar(1000); 		-- 声明所有节点机构号变量
    	declare chdTemp VARCHAR(1000) ;	-- 声明临时变量下级节点机构号
    	set chdTemp = orgId;
    	while chdTemp is not null do
    		if temp is null then set temp = chdTemp;
    		else set temp = concat(temp,',',chdTemp);
    		end if;
    		select GROUP_CONCAT(org_id) into chdTemp from org_info where FIND_IN_SET(parent_org_id,chdTemp) >0 ;
    	end while;
    	return temp;
    end;
    $$
    delimiter ;
    
    -- 调用存储函数，并查询下级机构列表
    select * from org_info where FIND_IN_SET(org_id,getLowOrgIds('100000'));

**查询上级：**

drop function  if exists getparentlist;
    delimiter $$
    create function getparentlist(orgid varchar(20)) returns varchar(1000)   
    begin   
        declare pid varchar(100);   					-- 声明单次循环上级机构号 的变量
        declare allpid varchar(1000) default orgid; -- 声明所有上级号的变量   
        while orgid is not null  do   
            set pid =(select parent_org_id as parentid from org_info where org_id = orgid);  
            if pid is not null then   
                set allpid = concat(allpid, ',', pid);         
            end if;   
    	    set orgid = pid;
        end while;   
        return allpid;  
    end;
    $$
    delimiter ;
    
    -- 调用存储函数，并查询上级机构列表
    select getparentlist('100007');
    select * from org_info where find_in_set(org_id,getparentlist('100007'));

**优点:** 简单，方便，没有递归调用层次深度的限制 (max\_sp\_recursion\_depth,最大255) ；

**缺点：**长度受限，虽然可以扩大 RETURNS varchar(1000)，但总是有最大限制的。

## 2、sql直接查询 ##

### 2.1、根据节点查询子节点： ###

**不包含自身：**

**方法一：**

-- 查询下级所有机构号，返回结果包含要查询的机构号'100000'
    select id from (
                  select t1.id,
                  if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild
                  from (
                       select org_id as id, parent_org_id as parent_id from org_info t order by parent_id, id
                      ) t1,
                      (select @pids := '100000') t2 -- '100000'为要查询的节点id。  变量定义。
                 ) t3 where ischild != 0;
    
    -- 查询下级所有机构号，并拼接成字符串,返回结果不包含要查询的机构号'100000'
    select GROUP_CONCAT(id) from (
                  select t1.id,
                  if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild
                  from (
                       select org_id as id, parent_org_id as parent_id from org_info t  order by parent_id, id
                      ) t1,
                      (select @pids := '100000') t2
                 ) t3 where ischild != 0;

**方法二：**

SELECT * FROM
    			 (
    			    SELECT * FROM org_info 
    			 ) T1,
    			 (SELECT @pid := '100000' ) T2
    		WHERE ((FIND_IN_SET(parent_org_id,@pid) > 0 AND @pid := CONCAT(@pid, ',', org_id)));

**包含自身：**

-- 查询下级所有机构号，返回结果包含要查询的机构号'100004'
    set @queryId = '100004'; -- 要查询的节点id
    select t3.* from (
                  select t1.*,
                  if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), if (t1.id =@queryId,@queryId,0)) as ischild
                  from (
                       select org_id as id, parent_org_id as parent_id,t.* from org_info t order by parent_id, id
                      ) t1,
                      (select @pids := @queryId) t2 -- '100004'为要查询的节点id。  变量定义。
                 ) t3 where ischild != 0;
    
    -- 查询下级所有机构号，并拼接成字符串,返回结果不包含要查询的机构号'100004'
    select GROUP_CONCAT(id) from (
                  select t1.id,
                  if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), if (t1.id =@queryId,@queryId,0)) as ischild
                  from (
                        select org_id as id, parent_org_id as parent_id,t.* from org_info t order by parent_id, id
                      ) t1,
                      (select @pids := @queryId) t2
                 ) t3 where ischild != 0;

说明：

>  *   @pids :='100000'是父节点赋值，此处同样可以改成普通条件查询赋值
> 
>     SELECT @pids := parent.id from org_info parent where parent.org_name = '测试机构100002';
> 
>  *  **if (find\_in\_set(t1.parent\_id, @pids) > 0, @pids := concat(@pids, ‘,’, t1.id), 0)**
> 
> 遍历表org\_info，判断parent\_id是否在@pids中，如果在就把当前节点加入@pids中，不在就标识为0.
> 
>  *  如果表较大，尽量增加一些条件过滤掉一些org\_info表中的一些数据.

### 2.2、根据节点查询父节点 ###

[参看文章][Link 1]

-- 查询机构号
    set @queryId = '100007';
    -- 执行查询上级机构
    SELECT d3.*,d2.lvl
        FROM ( 
            SELECT @r AS id, 
              (SELECT @r := parent_org_id FROM org_info where org_id = @r) AS tmp_parent_id,
    					@l := @l + 1 AS lvl -- 查询出上级机构的顺序（100007为1，直接上级为2，以此类推）
            FROM (SELECT @r := @queryId,@l := 0  ) leafNodeId, 
                 org_info hd) d2 
        INNER JOIN org_info d3 ON d2.id = d3.org_id AND d2.tmp_parent_id is not null
    ORDER BY d3.org_id;
    
    
    -- 或
    SELECT _id as orgId,parent_org_id, lvl   FROM (
    	SELECT   
    		@r AS _id,   
    		(SELECT @r := parent_org_id FROM org_info WHERE org_id = _id) AS parent_org_id,   
    		@l := @l + 1 AS lvl   
    	FROM   
    		(SELECT @r := @queryId, @l := 0) vars,   
    		org_info h   
    		WHERE @r <> 0 and @r <> '' and @r IS NOT NULL
    ) tmp

查询结果如下：

![20210108140003376.png][]

[Link 1]: https://blog.csdn.net/flzr2011/article/details/109397556?utm_medium=distribute.pc_relevant_bbs_down.none-task--2~all~sobaiduend~default-2.nonecase&depth_1-utm_source=distribute.pc_relevant_bbs_down.none-task--2~all~sobaiduend~default-2.nonecase
[20210108140003376.png]: https://img-blog.csdnimg.cn/20210108140003376.png