mysql查询树形结构上、下级

最新推荐文章于 2024-04-17 11:20:43 发布

炎升

最新推荐文章于 2024-04-17 11:20:43 发布

阅读量3.4k

点赞数 3

分类专栏： # mysql

本文链接：https://blog.csdn.net/weixin_40482816/article/details/112286241

版权

mysql 专栏收录该内容

49 篇文章 2 订阅

订阅专栏

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、根据节点查询父节点

参看文章

-- 查询机构号
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

查询结果如下：

炎升

关注

3
点赞
踩
15

收藏

觉得还不错? 一键收藏
4
评论
mysql查询树形结构上、下级

1、存储函数find_in_set(str,strlist) : 查询字符串str是否在strlist存在，返回存在字符串str的个数。str 要查询的字符串 strlist 字段名参数以”,”分隔如 (1,2,6,8) 查询字段(strlist)中包含(str)的结果，返回结果为null或记录假如字符串str在由N个子链组成的字符串列表strlist 中，则返回值的范围在 1 到 N 之间。一个字符串列表就是一个由一些被 ‘,’ 符号分开的子链组成的字符串。如果第一个参数是一个.
复制链接

扫一扫