Mis多级配方处理的简洁方式

Mis多级配方处理的简洁方式http://www.bieryun.com/3409.html

在Mis开发中经常会碰到配方(简易BOM)：如进销存中的组装单、拆卸单,MPR中的材料定额等均可以采用配方来解决。下边是一个常规树型配方的基本结构：

CREATE TABLE CL_CPPF(
XH varchar(30) not NULL,--型号
CXH varchar(30) not NULL,--子型号
PFSM varchar(30) NULL,--说明
SL numeric(18, 3) not NULL DEFAULT (0),--子型号数量
ID int IDENTITY(1,1) NOT NULL
)

网上可以查到多个类似配方处理的代码，大多是采用递归等方始处理的，代码较为复杂，有些时候只需深度为一、二级的小配方，不需要多级深度的大配方。能否用一条SQL语句就可以处理类似一、二级的小配方的呢？

通过努力，笔者终于找到了：
深度为一级的小配方：

Select case when a.sl is null then b.cxh else a.CXH end cxh,b.SL*isnull(a.sl,1) as sl From CL_CPPF as b
left JOIN CL_CPPF as a on a.xh=b.cxh where b.xh='DJ-001'

从语句上可以看出，一级的小配方仅仅是个左连查询。这真是大道至简呀。有了一级配方的语句，写二级配方就非常容易了：

Select case when a.sl is null then b.cxh else a.CXH end cxh,b.SL*isnull(a.sl,1) as sl From
(Select 'DJ-001' as xh,case when a.sl is null then b.cxh else a.CXH end cxh,b.SL*isnull(a.sl,1) as sl From CL_CPPF as b
left JOIN CL_CPPF as a on a.xh=b.cxh where b.xh='DJ-001' ) as b
left JOIN CL_CPPF as a on a.xh=b.cxh where b.xh='DJ-001'

下边是见证奇计的时刻了，
现隆重推出多级（无限级）配方的SQL代码：

declare @XH varchar(30)
declare @ID int,@ID1 int

CREATE TABLE #tmp (
XH varchar(30) NULL,
CXH varchar(30) NULL,
SL numeric(18, 3) NULL DEFAULT (0),
LVL Int NULL DEFAULT (0),--深度
ID int IDENTITY(1,1) NOT NULL
)

set @XH ='DJ-001'

insert into #tmp (xh,cxh,sl) select a.xh,a.cxh,a.sl from cl_CPPF as a where a.xh= @XH
set @id=0

while exists(select b.xh from #tmp as b, CL_CPPF as a where a.xh=b.cxh and b.xh=@XH and b.id>@id)
begin
select @id1=max(id) from #tmp
insert into #tmp (xh,cxh,sl,LVL)
Select @XH as xh,case when a.sl is null then b.cxh else a.CXH end cxh,b.SL*isnull(a.sl,1) as sl,b.LVL+1
From #tmp as b, CL_CPPF as a where a.xh=b.cxh and b.xh=@XH and b.id>@id
set @id=@id1
end

select * from #tmp as a where not exists( select xh from cl_CPPF as b where b.xh=a.cxh)
drop table #tmp

非常简单吧，上述多级（无限级）配方的SQL代码也可为编写其它复杂BOM时参考。

上述的代码是从产品主型号查找子材料的代码，作为问题的扩展，我们能否用子材料找出改材料被哪些主型号使用？
这里特别提示一下，代码中的XH 与 CXH 是对等。

上述观点仅供参考，代码上有疑问多联系，我们共同探讨。