sql递归查询子类

旧城等待， 2022-05-16 13:57 238阅读 0赞

平时工作中我们会遇到主从层次关系的结构数据，我们需要把数据取出来并且提现出层级就像树形结构一样，比如这样的结构：

![70][]

![70 1][]

数据库表结构如下，有个parent\_id和sub\_id，就是把两者的关系保存起来。

![70 2][]

id为768的下面有769,770,771,772,780,781数据，同时这些数据下面有可能有其它的数据，我们要查出768下面的所有数据可以使用mysql的函数来获取一条数据下面的子集

CREATE FUNCTION `getMaterialBomChildList`(`input_parent_id` integer) RETURNS varchar(1000) CHARSET utf8
    BEGIN  
          DECLARE sChildList VARCHAR(1000);  
          DECLARE sChildTemp VARCHAR(1000);  
          SET sChildTemp = cast(input_parent_id as CHAR);
    			# 循环递归
          WHILE sChildTemp is not null DO
            IF (sChildList is not null) THEN  
              SET sChildList = concat(sChildList,',',sChildTemp);  
    				ELSE  
    					SET sChildList = concat(sChildTemp);  
    				END IF;
      
            SELECT group_concat(sub_id) INTO sChildTemp FROM bs_material_bom where FIND_IN_SET(parent_id,sChildTemp)>0;  
          END WHILE;  
          RETURN sChildList;  
        END

这个函数的作用就是获取768下面的所有子集的id，我们使用navicat 查询这个函数

select getMaterialBomChildList(768) as subIds;

就能得到所有的子集

![70 3][]

我们在执行查询方法得到所有的数据

select * from bs_material_bom where FIND_IN_SET(parent_id,getMaterialBomChildList(768));

![70 4][]

利用存储过程查询出结果

CREATE PROCEDURE `getBomlList`(IN `mid` int)
    BEGIN
    	#Routine body goes here...
    	DROP TEMPORARY TABLE IF EXISTS bomlist;
    	DROP TEMPORARY TABLE IF EXISTS bomlist_copy;
    	CREATE TEMPORARY TABLE IF NOT EXISTS bomlist(
    		mid INTEGER NOT NULL,
    		qty INT NOT NULL DEFAULT 0,
    		lct  VARCHAR(1024) NULL,
    		mpath VARCHAR(1024) NULL,
    		lid INTEGER NOT NULL
    	);
    	CREATE TEMPORARY TABLE IF NOT EXISTS bomlist_copy(
    			mid INTEGER NOT NULL,
    			qty INT NOT NULL DEFAULT 0,
    			lct  VARCHAR(1024) NULL,
    			mpath VARCHAR(1024) NULL,
    			lid INTEGER NOT NULL
    		);
    
    	-- init
    	SET @lid = 1;
    	DELETE FROM bomlist;
    	DELETE FROM bomlist_copy;
    	INSERT INTO bomlist(mid, qty, lct, mpath, lid) values(mid, 1, '', mid, @lid);
    	INSERT INTO bomlist_copy(mid, qty, lct, mpath, lid) values(mid, 1, '', mid, @lid);
    
    	-- get sub mtls
    	WHILE
    		EXISTS (SELECT 1 FROM bomlist_copy A INNER JOIN bs_material_bom B ON A.mid = B.parent_id WHERE A.lid = @lid)
    		-- 避免死循环
    		AND @lid<10 	-- 简化处理
    		-- AND NOT EXISTS(SELECT 1 FROM bomlist_copy C LEFT JOIN bs_material_bom D ON C.mid = D.parent_id WHERE C.lid = @lid AND B.sub_id in (SELECT mid FROM bomlist))
    	DO
    		-- get sub mtls
    		INSERT INTO bomlist(mid, qty, lct, mpath, lid) SELECT B.sub_id, B.qty, B.location, CONCAT(A.mpath, ',', B.sub_id), (@lid+1) AS new_lid FROM bomlist_copy A INNER JOIN bs_material_bom B ON A.mid = B.parent_id WHERE A.lid = @lid;
    	
    		-- avoid reopen temporaty table
    		INSERT INTO bomlist_copy(mid, qty, lct, mpath, lid) SELECT * FROM bomlist WHERE lid = (@lid + 1);
    
    		-- init next level
    		SET @lid = @lid + 1;
    	END WHILE;	
    
    	SELECT A.*, B.material_code, B.description
    	FROM bomlist A LEFT JOIN bs_materials B ON A.mid=B.id
    	ORDER BY A.mpath;
    END

查询结果

call getBomlList(768);

![70 5][]

[70]: /images/20220516/95ab7f267c6147efa4e90bd2ed4137fd.png
[70 1]: /images/20220516/52555a696a9841aba990d8f90903ff2d.png
[70 2]: /images/20220516/fd0fe6da7c914028baed0d6e0bdef565.png
[70 3]: /images/20220516/620e580e442e45f5885cf5eb1b6fd095.png
[70 4]: /images/20220516/07cd935022e94b26b74ef48f852dc2d6.png
[70 5]: /images/20220516/bd233bae61fc4b8ca6e0ba6358b69787.png