平時(shí)工作中我們會(huì)遇到主從層次關(guān)系的結(jié)構(gòu)數(shù)據(jù)，我們需要把數(shù)據(jù)取出來并且提現(xiàn)出層級(jí)就像樹形結(jié)構(gòu)一樣，比如這樣的結(jié)構(gòu)：

數(shù)據(jù)庫表結(jié)構(gòu)如下，有個(gè)parent_id和sub_id，就是把兩者的關(guān)系保存起來。

id為768的下面有769,770,771,772,780,781數(shù)據(jù)，同時(shí)這些數(shù)據(jù)下面有可能有其它的數(shù)據(jù)，我們要查出768下面的所有數(shù)據(jù)可以使用mysql的函數(shù)來獲取一條數(shù)據(jù)下面的子集

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);# 循環(huán)遞歸WHILE sChildTemp is not null DOIF (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

這個(gè)函數(shù)的作用就是獲取768下面的所有子集的id，我們使用navicat 查詢這個(gè)函數(shù)

select getMaterialBomChildList(768) as subIds;

就能得到所有的子集

?我們?cè)趫?zhí)行查詢方法得到所有的數(shù)據(jù)

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

利用存儲(chǔ)過程查詢出結(jié)果

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);-- initSET @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 mtlsWHILEEXISTS (SELECT 1 FROM bomlist_copy A INNER JOIN bs_material_bom B ON A.mid = B.parent_id WHERE A.lid = @lid)-- 避免死循環(huán)AND @lid<10 -- 簡(jiǎn)化處理-- 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 mtlsINSERT 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 tableINSERT INTO bomlist_copy(mid, qty, lct, mpath, lid) SELECT * FROM bomlist WHERE lid = (@lid + 1);-- init next levelSET @lid = @lid + 1;END WHILE; SELECT A.*, B.material_code, B.descriptionFROM bomlist A LEFT JOIN bs_materials B ON A.mid=B.idORDER BY A.mpath; END

查詢結(jié)果

call getBomlList(768);

總結(jié)

以上是生活随笔為你收集整理的sql递归查询子类的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。