平时工作中我们会遇到主从层次关系的结构数据,我们需要把数据取出来并且提现出层级就像树形结构一样,比如这样的结构:
数据库表结构如下,有个parent_id和sub_id,就是把两者的关系保存起来。
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;
就能得到所有的子集
我们在执行查询方法得到所有的数据
select * from bs_material_bom where FIND_IN_SET(parent_id,getMaterialBomChildList(768));
利用存储过程查询出结果
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);