排列组合问

[DeD]

某个P物料由N个子物料组成，其中每个物料有M种价格，如何用VB或SQL实现所有组成P的价格列表?

[yangzi]

呵呵，300分太多了。把人吓住了。

[wwq_jeff]

[method one]
比如P物料下有以下子物料：
bomtable
FParentID FChildID
-----------------
p p1
p p2
....
p pn


根据字物料从价格表中得到相应的价格子表
pirce1
FItemID FPrice
----------
p1 10.0
p1 12.0

pirce2
FItemID FPrice
----------
p2 20.0
p2 22.0

........

pircen
FItemID FPrice
----------
pn 10.0
pn 12.0


select u1.*,u2.*,...,un.* from price1 u1,price2 u2,...,pricen un

[method two]
用上述办法可能SQL比较长，也不易组织和维护，可以改进SQL大致如下：

declare @lcount int
declare @ItemID int
declare @oldtable varchar(100)
declare @newtable varchar(100)
declare @strsql varchar(4000)

set @lcount = 0
declare cur_item cursor for
select fchildid from bomtable
open cur_item
fetch cur_item into @itemid
while (@@fetch_status = 0)
begin
set @lcount = @lcount + 1
---得到该物料价格 #itemprice

if n > 1
begin
set @newtable = "#price" + convert(varchar(100),n)
set @strsql = "select v1.*,v2.* into " + @newtable
set @strsql = @strsql + " from #itemprice v1," + @oldtable + " v2 "
set @strsql = "drop table " & oldtable

end
else
begin
set @newtable = "#price" + convert(varchar(100),n)
set @strsql = "select v1.* into " + @newtable + " v1 "
end
set @oldtable = @newtable
execute (strsql)

fetch cur_item into @itemid
end
drop table #itemprice
close cur_item
DEALLOCATE cur_item

[guo]

假设各原料表结构为n1的价格表为id,n1目标表结构为n1,n2,n3...nn,
整个问题的解决就是N个游标的组合操作,如定位第一个子物料的第一条记录,和其它子物料表的第一条记录插入到目标表中,此为第一个组合结果.同样的M*N次循环后可以将所有组合结果插入到目标表中.

[wwq_jeff]

sorry,[method two]中有些不完整
if n > 1
begin
set @newtable = "#price" + convert(varchar(100),n)
set @strsql = "select v1.*,v2.* into " + @newtable
set @strsql = @strsql + " from #itemprice v1," + @oldtable + " v2 "
set @strsql = "drop table " & oldtable

end
else
begin
set @newtable = "#price" + convert(varchar(100),n)
set @strsql = "select v1.* into " + @newtable + " from #itemprice v1 "
-------------------
end
set @oldtable = @newtable
execute (strsql)

[DeD]

还有没有更多的建议？先派部分红利

[LI_xiufu]

楼上老兄尚未考虑不同组合的问题，
假设数据表为 Tprice:P,Px,price
第一步
select distinct p,price from Tprice
得到各种单一价格
第二步...要下班了明天再说

[hughie]

说明，不要被下面的一大堆代码吓到
其实你只要做的就是给 变量@m 和 @mm1付值
因为我用了取巧的方法，所以要求付值时有格式。
@m 表示你的子物料数 就是n
@mm1 的格式为（子物料1的索引，价格1| 子物料1的索引，价格2 |。。。）
你可以用下面的我付好值的语句试一下
如果格式不清楚再问。

declare @mm1 varchar(3000)
declare @n int

set @n = 3
select @mm1 = '1,2.8|1,5|1,6|2,2|2,6|2,9|3,9|3,3.6|3,5'

CREATE TABLE #dd (
[p] [int] NULL ,
[m] [numeric] (18,3) NULL )
declare @sqltext varchar(8000)
if @mm1 <> ''
begin
select @sqltext = 'insert into #dd (p,m) values ('+replace(@mm1,'|',') insert into #dd (p,m) values (')+')'
exec(@sqltext)
end

declare @i int
set @i = 2
select @sqltext ='select m from #dd where p = 1'
while @i<=@n
begin
select @sqltext = 'select a.m+b.m as m from (select m from #dd where p = '+cast(@i as varchar(30))+') a cross join ('+@sqltext+') b'
select @i =@i+1
end

exec(@sqltext)
select * from #dd

drop table #dd

[nononono]

有那么复杂吗？对3个表按代码连接不可以吗？如下：

select a.物料,b.子物料,c.价格
from a,b,c
where a.物料=b.物料 and a.物料=c.物料

a表：物料表
b表：子物料表
c表：物料价格表

[hughie]

to nononono(null,null)　
你读懂ded的意思了吗？

[liming_zh]

10个物料，10个子物料，与10个价格
的结果集有多大？

[nononono]

hughie(雨神)，你认为哪里没明白？

[nononono]

liming_zh(liming), 子物料、价格要与物料连接，即使“10个物料，10个子物料，与10个价格”，结构还不是10*10*10=1000。如果要的到1000个，应该是下面这样的：

select a.物料,b.子物料,c.价格 from a,b,c

呵呵。

[hughie]

==================================================================
wwq_jeff(飞雪)　我的这个问题涉及的是生产计划管理里面BOM的成本计算的比较
例如：
A物料是由B、C、D三个子物料构成
而B物料有三种不同的采购价格 B1 B2 B3
C物料有三种不同的采购价格 C1 C2 C3
D物料有三种不同的采购价格 D1 D2 D3
我需要最后得到 B1+C1+D1......B3+C3+D3这样不同的9种价格组合
当然在考虑的过程中需要考虑相应的性能，因为可能我处理的BOM有几千个子料
每个子料有几十种价格
===================================================================

b用1代替，c从2代替 d用3代替
把价格都换成数字
你用下面的脚本试一试
得到一个27条纪录的结果集，字段只有一个，就是物料的不同组合的合计成本值.

declare @mm1 varchar(3000)
declare @n int

set @n = 3
select @mm1 = 'b,b1¦b,b2¦b,b3¦c,c1¦c,c2¦c,c3¦d,d1¦d,d2¦d,d3'

CREATE TABLE #dd (
[p] [int] NULL ,
[m] [numeric] (18,3) NULL )
declare @sqltext varchar(8000)
if @mm1 <> ''
begin
select @sqltext = 'insert into #dd (p,m) values ('+replace(@mm1,'¦',') insert into #dd (p,m) values (')+')'
exec(@sqltext)
end

declare @i int
set @i = 2
select @sqltext ='select m from #dd where p = 1'
while @i<=@n
begin
select @sqltext = 'select a.m+b.m as m from (select m from #dd where p = '+cast(@i as varchar(30))+') a cross join ('+@sqltext+') b'
select @i =@i+1
end

exec(@sqltext)
select * from #dd

drop table #dd


如果子物料增多，有多少子物料就把@n 得值付成子物料的种类数就可以了，
如果价格套数增多，那就把@mm1 里相应的值增多，记住因为用了点取巧的东西，所以物料应定要用连续的数字代替。
我想你应定会明白怎么给值吧
其实你最好把这段脚本封到存储过程里。

[wwq_jeff]

呵呵，好多想法啊！
其实hughie(雨神)兄的算法和我第二种算法类似，只不过他返回了成本计算的结果。我返回的是所有价格的组合。

LI_xiufu(琅琊观海)的算法也很好的，但如果数据太多的情况下，这个SQL是无法控制长度的，也无法提交执行，好象SQL的执行是有长度限制的。

[nononono]

DeD(似水年华)并没有说要横向的组合。这样横向的组合也有多种形式。

。

[wwq_jeff]

需要结果是这样的(因为没法写函数，相信应该明白)：

c(N1,1) * c(N2,1) * ....* c(Nn,1)

需要穷举所有的价格组合，是横向排列，不是nononono(null,null)那样的的纵向排列。
如果用VB程序，恐怕需要做数据模型，用矩阵算法可以实现（比较麻烦，因为还有许多特殊处理的）。但是SQL的笛卡儿乘积可以为我们提供方便，我的算法也是从笛卡儿乘积演变而来。希望各位大虾提供更多的想法，一起学习。

[wwq_jeff]

我的这个问题涉及的是生产计划管理里面BOM的成本计算的比较
例如：
A物料是由B、C、D三个子物料构成
而B物料有三种不同的采购价格 B1 B2 B3
C物料有三种不同的采购价格 C1 C2 C3
D物料有三种不同的采购价格 D1 D2 D3
我需要最后得到 B1+C1+D1......B3+C3+D3这样不同的9种价格组合
当然在考虑的过程中需要考虑相应的性能，因为可能我处理的BOM有几千个子料
每个子料有几十种价格

[LI_xiufu]

补充昨天：
declare @renum int --不同P的不同单价数
declare @Id int不清 --循环变量
declare @str varchar (8000) --总串
declare @S_str varchar(8000) --select 串
declare @F_str varchar(8000) --from 串
declare @W_str varchar(8000) --where 串
declare @tablename varchar(10) --表别名
declare @Ftablename varchar(10) --前一表别名
declare @pname varchar(10) --单价别名
declare @idp int --第二循环变量
declare @case_str varchar(8000) --case串
--drop table #temp
create table #temp (p char(10),price decimal(18,2))
insert into #temp select distinct p,price from pprice
select @renum=count(*) from #temp
set @id=1
set @S_str='select a.p,a.price'
set @F_str=' from #temp a'
set @W_str=' where a.p=a.p'

if @renum=1
begin
set @str=@S_str+@F_str
end else
begin
--set @case_str=' case when a.price'
while @id<=@renum-1
begin
set @tablename='a'+convert(varchar,@ID)
set @pname='price'+convert(varchar,@ID)
if @id<2
begin
set @S_str=@S_str+','+@pname+'= case when a.price=a1.price then 0 else a1.price end'
end else
begin
set @idp=1
set @case_str=' case when a.price='+@tablename+'.price'
while @idp<=@iD-1
begin
set @Ftablename='a'+convert(varchar,@IDp)
--set @ppname='price'+convert(varchar,@IDp)
set @case_str=@case_str+' or '+@Ftablename+'.price='+@tablename+'.price'
set @idp=@idp+1
end
set @case_str=@case_str+' then 0 else '+@tablename+'.price end'
set @S_str=@s_str+','+@pname+'='+@case_str
end
set @F_str=@F_str+',#temp '+@tablename
set @W_str=@W_str+' and a.p='+@tablename+'.p'
set @id=@id+1
end
set @str=@S_str+@F_str+@W_str
end
set @str='select distinct a.p,a.pricet from (select a.p,pricet=a.price+a.price1+a.price2+... from (' +@str+') a) a'
---此句也可用循环

exec(@str)
表的记录
p px price
-----------------
p p1 1.2
p p2 1.6
p p2 1.8
相加前@str的结果
p price price1 price2
---------- -------------------- -------------------- --------------------
p 1.20 .00 1.80
p 1.60 1.20 1.80
p 1.80 1.20 .00
p 1.20 .00 1.60
p 1.60 1.20 .00
p 1.80 1.20 1.60
p 1.20 .00 .00
p 1.60 1.20 .00
p 1.80 1.20 .00
p 1.20 1.60 1.80
p 1.60 .00 1.80
p 1.80 1.60 .00
p 1.20 1.60 .00
p 1.60 .00 .00
p 1.80 1.60 .00
p 1.20 1.60 .00
p 1.60 .00 1.20
p 1.80 1.60 1.20
p 1.20 1.80 .00
p 1.60 1.80 .00
p 1.80 .00 .00
p 1.20 1.80 1.60
p 1.60 1.80 .00
p 1.80 .00 1.60
p 1.20 1.80 .00
p 1.60 1.80 1.20
p 1.80 .00 1.20
最后结果
p pricet
---------- ----------------------
p 1.20
p 1.60
p 1.80
p 2.80
p 3.00
p 3.40
p 4.60

[wwq_jeff]

我做过的生产计划管理系统中也有这样的设想，我只是从我的了解认为应该是这种横排的所有组合，而不仅仅是价格累计，因为最后用户应该可以看到是那些供应商提供的价格且价格是多少。

也许我的理解有些偏差，希望大家共同探讨。这是个很实用的问题。

[hp1200]

up