求bom表正向展开SQL语言或储存过程(100)

BOM表內，已知成品編號，查找它下面所有子部品的SQL語言怎么實現？

如果是Oracle数据库，可以通过Oracle层级查询来实现，案例：select level ,id,classname,fatherid from vgq_class where 其它条件start with id= '20055'connect by prior id = fatherid--父类和子类的关系

如果BOM限定为10~20层以下，可一次选出，再用代码处理成需要形态：select L1.ItemNo as L1ItemNo,...,Ln.ItemNo as LnItemNo from BOM as L1left join BOM as L2 where l2.ParentItemNo=l1.ItemNo...left join BOM as Ln where ln.ParentItemNo=ln-1.ItemNowhere L1.ParentItemNo='成品編號'如果未限定层数，可一次选4~5层，再递归。

1楼竟然与我想法相同，不重复回复了，虎虎！

看你数据库结构怎么做的，根据层次表去做

數據庫是SQL,不是Oracle数据库

递归

自己编写，调用金碟K3的表!/****** 对象: UserDefinedFunction [dbo].[GetBOMTreeInfo] 脚本日期: 05/23/2009 17:22:27 编写:CMJ******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER ONGO/****** 对象: 用户定义函数 dbo.GetBOMTreeInfo 2.0版本 脚本日期: 2007-09-19 20:18:43 ******/ALTER FUNCTION [dbo].[GetBOMTreeInfo] ( @ItemID as int) RETURNS @treeinfo table ( [fid] [int] IDENTITY (1, 1) NOT NULL , --真正的结点ID [id] [int] NOT NULL ,--结点内码[name] [varchar] (100) NOT NULL, --产品名称[parentid] [int] NOT NULL,--父结点内码 [itemid][int],--产品内码[Model][varchar](128),--规格型号[ERPClsID][int],--产品属性内码[DefaultRoutingID][int],--默认工艺路线内码[BomNum][varchar](300),--BOM单号[levels] [int] NOT NULL ,--BOM层次[fYield][decimal](18,2),--成品率[fScrap][decimal](18,2),--损耗率[fAuxQty][decimal](18,4),--用量[fNumber][varchar](255),--代码[fChartNumber][varchar](255),--图号[fUnitName][varchar](32),--产品单位[fRealStockNum][decimal](18,2),--实仓数量[fDummyStockNum][decimal](18,2),--虚仓数量--fReferNum[decimal](18,2),参照数量[fErpClsName][varchar](32),--产品属性名称[fUnitID][int],[fQty][decimal](18,10),--基本单位用量[fCZNum][decimal](24,10)--参照数) AS BEGIN declare @level as int,@BOMID as intselect @level=0select @bomid=finterid from icbom where fitemid=@ItemID and fusestatus=1072INSERT INTO @treeinfo SELECT a.finterid,b.fname,-1,a.fitemid,b.fmodel,b.ferpclsid,b.fdefaultroutingid,a.fbomnumber,@level,fYield,0,1,b.fnumber,b.fchartnumber, f.fname as fUnitName,g.fqty as fRealNumber,h.fqty as fDummyNumber,i.fname as ferpclsname,a.funitid,1,0FROM icbom a inner join t_icitem b on a.fitemid=b.fitemid left JOIN dbo.t_MeasureUnit f ON a.FUnitID = f.FMeasureUnitID left JOIN (SELECT fitemid, SUM(fqty) AS fqty FROM icinventory GROUP BY fitemid) g ON b.FItemID = g.fitemid LEFT JOIN (SELECT fitemid, SUM(fqty) AS fqty FROM poinventory GROUP BY fitemid) h ON b.FItemID = h.fitemid left join (select finterid,fname from t_submessage where ftypeid=210 and fparentid=0) i on b.ferpclsid=i.finteridwhere a.finterid=@BOMIDif @@rowcount=0 beginINSERT INTO @treeinfo SELECT @itemid,b.fname,-1,b.fitemid,b.fmodel,b.ferpclsid,b.fdefaultroutingid,'',@level,100,0,1,b.fnumber,b.fchartnumber, f.fname as fUnitName,g.fqty as fRealNumber,h.fqty as fDummyNumber,i.fname as ferpclsname,b.funitid,1,0FROM t_icitem b left JOIN dbo.t_MeasureUnit f ON b.FUnitID = f.FMeasureUnitID left JOIN (SELECT fitemid, SUM(fqty) AS fqty FROM icinventory GROUP BY fitemid) g ON b.FItemID = g.fitemid LEFT JOIN (SELECT fitemid, SUM(fqty) AS fqty FROM poinventory GROUP BY fitemid) h ON b.FItemID = h.fitemid left join (select finterid,fname from t_submessage where ftypeid=210 and fparentid=0) i on b.ferpclsid=i.finteridwhere b.fitemid=@itemid and b.ferpclsid<>2 goto MetEndendWhile 1=1begin insert into @treeinfo select a.fentryid,b.fname,z.fid,a.fitemid,b.fmodel,b.ferpclsid,b.fdefaultroutingid,Null,@level+1,100,a.fScrap,a.fAuxQty,b.fnumber,b.fchartnumber, f.fname as fUnitName,g.fqty as fRealNumber,h.fqty as fDummyNumber,i.fname as ferpclsname,a.funitid,a.fQty,0 from (select * from @treeinfo where levels=@level) z left join icbomchild a on a.finterid=z.id inner join t_icitem b on a.fitemid=b.fitemid and b.ferpclsid<>2 left JOIN dbo.t_MeasureUnit f ON a.FUnitID = f.FMeasureUnitID left JOIN (SELECT fitemid, SUM(fqty) AS fqty FROM icinventory GROUP BY fitemid) g ON b.FItemID = g.fitemid LEFT JOIN (SELECT fitemid, SUM(fqty) AS fqty FROM poinventory GROUP BY fitemid) h ON b.FItemID = h.fitemid left join (select finterid,fname from t_submessage where ftypeid=210 and fparentid=0) i on b.ferpclsid=i.finterid insert into @treeinfo select a.finterid,b.fname,c.fid,a.fitemid,b.fmodel,b.ferpclsid,b.fdefaultroutingid,a.fbomnumber,@level+1,fYield,fScrap,1,b.fnumber,b.fchartnumber, f.fname as fUnitName,g.fqty as fRealNumber,h.fqty as fDummyNumber,i.fname as ferpclsname,a.funitid,1,0 from icbom a inner join t_icitem b on a.fitemid=b.fitemid and b.ferpclsid=2 inner join (select a.finterid,a.fitemid,a.fScrap,b.fid from icbomchild a inner join @treeinfo b on a.finterid=b.id and b.levels=@level) c on a.fitemid=c.fitemid left JOIN dbo.t_MeasureUnit f ON a.FUnitID = f.FMeasureUnitID left JOIN (SELECT fitemid, SUM(fqty) AS fqty FROM icinventory GROUP BY fitemid) g ON b.FItemID = g.fitemid LEFT JOIN (SELECT fitemid, SUM(fqty) AS fqty FROM poinventory GROUP BY fitemid) h ON b.FItemID = h.fitemid left join (select finterid,fname from t_submessage where ftypeid=210 and fparentid=0) i on b.ferpclsid=i.finterid where a.fusestatus=1072 if @@rowcount=0 break else select @level=@level+1 endupdate a set a.fauxqty=b.fauxqty from @treeinfo a inner join icbomchild b on a.itemid=b.fitemid and a.erpclsid=2 and a.levels<>0MetEnd:RETURN END

足级展开，直到没有子。。

用SQL 2005超级简单表结构：CREATE TABLE [dbo].[DIRT]( [DIRT_SELF_EXT] [int] IDENTITY(1,1) NOT NULL,--自身结点编号 [DIRT_PART_EXT] [int] NULL, --父结点号，如是根则设为0 [DIRT_NAM] [nvarchar](200) NOT NULL --自身结点名称) ON [PRIMARY]WITH A(DIRT_SELF_EXT,DIRT_PART_EXT,DIRT_NAM,lev)AS(SELECT DIRT_SELF_EXT,DIRT_PART_EXT,DIRT_NAM,0 as lev FROM DIRTWHERE DIRT_SELF_EXT=11 ---11可设为变量，表示传入要展开的产品的自身结点号UNION ALLSELECT DIRT.DIRT_SELF_EXT,DIRT.DIRT_PART_EXT,DIRT.DIRT_NAM,lev+1 FROM DIRTINNER JOIN A ON DIRT.DIRT_PART_EXT=A.DIRT_SELF_EXT) SELECT * FROM A