如何通过SQL查询BOM层级关系

一、SQLSERVER版

二、ORACLE版

注意事项：若BOM存在嵌套，会导致数据库出现问题。尽量采用webapi调用

一、SQLSERVER版

CREATE PROCEDURE BOMEXPAND @BOMID INT
AS
BEGIN
        -- 删除临时表
	if object_id(N'tempdb..#BOMSourceTable',N'U') is not null
		DROP TABLE #BOMSourceTable;
        -- 将源表插入到临时表
	select b.fid,m.fnumber AS pm,bc.fseq,bc.fentryid,m1.fnumber AS cm,(case when bc.fbomid>0 then bc.fbomid else (select max(fid) from t_eng_bom where fmaterialid=bc.fmaterialid) end) fbomid 
		into #BOMSourceTable
		from t_eng_bom b
	inner join t_eng_bomchild bc on bc.fid=b.fid
	inner join t_bd_material m on m.fmaterialid = b.fmaterialid
	inner join t_bd_material m1 on m1.fmaterialid = bc.fmaterialid
	 where b.fforbidstatus='A' AND B.FDocumentStatus='C';  --条件可自行指定（非禁用且已审核）
        --  递归查询
	with cte_child as
	(
	 --起始条件
		select top 1 1 lv,convert(varchar(256),fentryid) fullPath,*
		from #BOMSourceTable
		where fid = @BOMID	--指定起始行
		union all
		--递归条件
		select  (b.lv+1) lv,convert(varchar(256),(b.fullPath +'->'+convert(varchar(256),a.fentryid))) fullPath,a.*
		from #BOMSourceTable a
		inner join 
		cte_child b
		on 
		  ( a.fid=b.fbomid) -- 父找子  -- 218690
		 -- ( a.fbomid=b.fid) -- 子找父  -- 218688
	)
	select * from cte_child order by lv,fseq;
END;

-- 执行测试
EXEC BOMEXPAND 218690;

效果图：

视图版本

IF EXISTS (SELECT 1 FROM sys.views where name='V_BomSoutce') DROP VIEW V_BomSoutce;
GO
CREATE VIEW V_BomSoutce	
as
select b.fid,m.fnumber AS pm,bc.fseq,bc.fentryid,m1.fnumber AS cm,(case when bc.fbomid>0 then bc.fbomid else (select max(fid) from t_eng_bom where fmaterialid=bc.fmaterialid) end) fbomid 
		from t_eng_bom b
	inner join t_eng_bomchild bc on bc.fid=b.fid
	inner join t_bd_material m on m.fmaterialid = b.fmaterialid
	inner join t_bd_material m1 on m1.fmaterialid = bc.fmaterialid
	 where b.fforbidstatus='A' AND B.FDocumentStatus='C'

CREATE PROCEDURE BOMEXPAND @BOMID INT
AS
BEGIN
        --  递归查询
	with cte_child as
	(
	 --起始条件
		select top 1 1 lv,convert(varchar(256),fentryid) fullPath,*
		from V_BomSoutce	
		where fid = @BOMID	--指定起始行
		union all
		--递归条件
		select  (b.lv+1) lv,convert(varchar(256),(b.fullPath +'->'+convert(varchar(256),a.fentryid))) fullPath,a.*
		from V_BomSoutce	a
		inner join 
		cte_child b
		on 
		  ( a.fid=b.fbomid) -- 父找子  -- 218690
		 -- ( a.fbomid=b.fid) -- 子找父  -- 218688
	)
	select * from cte_child order by lv,fseq;
END;

-- 执行测试
EXEC BOMEXPAND 218690;

二、ORACLE版

select level,sys_connect_by_path(t1.fentryid,'->') fullPath,t1.* from (
   select b.fid,m.fnumber AS pm,bc.fseq,bc.fentryid,m1.fnumber AS cm,(case when bc.fbomid>0 then bc.fbomid else (select max(fid) from t_eng_bom where fmaterialid=bc.fmaterialid) end) fbomid from t_eng_bom b
    inner join t_eng_bomchild bc on bc.fid=b.fid
    inner join t_bd_material m on m.fmaterialid = b.fmaterialid
    inner join t_bd_material m1 on m1.fmaterialid = bc.fmaterialid
    where b.fforbidstatus='A'
) t1
start with fid = 736412      -- 起始BOMId
connect by prior fid=fbomid    -- 子找父 示例：736412
-- connect by prior fbomid=fid  -- 父找子 示例: 669876
order by level,fseq;

效果图：