一个棘手的问题

[wolflove23]

父物料代码 子物料代码 台量
A A1 2 //A由2个A1和1个A2组成
A A2 1
A1 A11 1 //A1又由1个A11和2个A22组成
A1 A1 2
想查询出A最终由以下子物料代码组成：

A A11 2
A A12 4
A A2 1
请问这样的查询怎么写代码？或者说像这种结构怎样组织一张表呢？

[一者仁心]

我的理解能力有问题了 看不明白乐

[中国风]

create table T(ParentID nvarchar(5), ID nvarchar(5),num int)
insert T values('A', 'A1',2)  --   //A由2个A1和1个A2组成 
insert T values('A' ,'A2',   1) 
insert T values('A1', 'A11', 1) --//A1又由1个A11和2个A22组成 
insert T values('A1', 'A1',  2) 

go

create function F_Num(@ParentID nvarchar(5),@num int=1)
returns @T table(ID nvarchar(5),num int)
as
begin
	declare @i int
	set @i=1
	declare @T2 table( ParentID nvarchar(5), ID nvarchar(5),num int,lev int)
	insert @T2
	select *,num*@num from T where ParentID=@ParentID
	while @@rowcount>0
		begin
			set @i=@i+1
			insert @T2
			select
				a.ParentID,
				a.ID,
				a.num*b.Num,
				@i
			from 
				T a join @T2 b on a.ParentID=b.ID and b.lev=@i-1
		end
	insert @T
	select ID,sum(Num) from @T2 a where not exists(select 1 from @T2 where ParentID=a.ID) group by ID
	
	return 
end

go

select * from F_Num('A',1)

select * from F_Num('A1',1)

ID    num         
----- ----------- 
A1    2
A2    1

（所影响的行数为 2 行）

ID    num         
----- ----------- 
A11   1

（所影响的行数为 1 行）

[wolflove23]

其实这是一个树结构（数据结构）我有一个产品A 它由产品A1 和A2 组成，而A1 又有A11和A12组成。我最上面的帖子写的时候把A12错写成A1 了，不好意思。
父物料代码 子物料代码 台量
A A1 2 //A由2个A1和1个A2组成
A A2 1
A1 A11 1 //A1又由1个A11和2个A22组成
A1 A12 2
想查询出A最终由以下子物料代码组成：

A A11 2
A A12 4
A A2 1
我是想计算一个如果有5件产品A，最后他到底需要多少原材料，就是A11，A12，A2分别多少。是不是需要用嵌套查询。

[中国风]

create table T(ParentID nvarchar(5), ID nvarchar(5),num int)
insert T values('A', 'A1',2)  --   //A由2个A1和1个A2组成 
insert T values('A' ,'A2',   1) 
insert T values('A1', 'A11', 1) --//A1又由1个A11和2个A22组成 
insert T values('A1', 'A12',  2) 

go

create function F_Num(@ParentID nvarchar(5),@num int=1)
returns @T table(ID nvarchar(5),num int)
as
begin
    declare @i int
    set @i=1
    declare @T2 table( ParentID nvarchar(5), ID nvarchar(5),num int,lev int)
    insert @T2
    select 
		ParentID,ID,num*@num,@i --这里改一下手误
	from T 
	where 
		ParentID=@ParentID
    while @@rowcount>0
        begin
            set @i=@i+1
            insert @T2
            select
                a.ParentID,
                a.ID,
                a.num*b.Num,
                @i
            from 
                T a join @T2 b on a.ParentID=b.ID and b.lev=@i-1
        end
    insert @T
    select ID,sum(Num) from @T2 a where not exists(select 1 from @T2 where ParentID=a.ID) group by ID
    
    return 
end

go

select * from F_Num('A',1)

select * from F_Num('A1',1)


ID    num         
----- ----------- 
A11   2
A12   4
A2    1

（所影响的行数为 3 行）

ID    num         
----- ----------- 
A11   1
A12   2

（所影响的行数为 2 行）

[中国风]

select *
from
F_Num('A',1)--这里的1为要组合的数量，可定义



go

[wolflove23]

好复杂，看不懂了。像这样结构大家伙该如何设计表呢？

[中国风]

以上是一个递归...

@num--需要生成的数量,也就是生成几个产品

num*@num--需要下一级的组成数量

@@rowcount>0--影响记录


@i--记录递归的层数


select ID,sum(Num) from @T2 a where not exists(select 1 from @T2 where ParentID=a.ID) group by ID
--取表变量里没有子级的记录，并求和



这里有关于递归的列子
http://blog.csdn.net/roy_88/archive/2006/12/24/1458449.aspx

[wolflove23]

想做个类似于ERP系统的数据库，其中一项就是根据订单的产品件算出它所用的原材料。其中储存产品结构的表大概有1、2万行数据。和上面的结构类似。不知道这个递归函数的执行效率如何？