现有BOM表的内容如下
BelongGoodsNo ParentGoodsNo GoodsNo Qty ChildQty
F66F00 F66F00140000 F66F00141000 4.54 1
F66F00 F66F00150000 F66F00141000 4.54 1
F66F00 F66F00160000 F66F00141000 4.54 1
F66F00 F66F00141000 F66F00141001 4.54 2
F66F00 F66F00141001 01300000100400 4.5 1
F66F00 F66F00141001 01300000100200 0.04 1实际上表树结构是如下:
F66F00--F66F00140000--F66F00141000--01300000100400
--01300000100200
--F66F00150000--F66F00141000--01300000100400
--01300000100200
--F66F00160000--F66F00141000--01300000100400
--01300000100200
现要求出F66F00这个产品的重量,请问SQL怎么写?
BelongGoodsNo ParentGoodsNo GoodsNo Qty ChildQty
F66F00 F66F00140000 F66F00141000 4.54 1
F66F00 F66F00150000 F66F00141000 4.54 1
F66F00 F66F00160000 F66F00141000 4.54 1
F66F00 F66F00141000 F66F00141001 4.54 2
F66F00 F66F00141001 01300000100400 4.5 1
F66F00 F66F00141001 01300000100200 0.04 1实际上表树结构是如下:
F66F00--F66F00140000--F66F00141000--01300000100400
--01300000100200
--F66F00150000--F66F00141000--01300000100400
--01300000100200
--F66F00160000--F66F00141000--01300000100400
--01300000100200
现要求出F66F00这个产品的重量,请问SQL怎么写?
-------------------------------------------
参考:
http://community.csdn.net/Expert/topic/4782/4782023.xml?temp=.4853632create table tb1(ChildId int,Fatherid int)
insert into tb1
select 1,0 union all
select 2,0 union all
select 3,1 union all
select 4,1 union all
select 5,2 union all
select 6,2 union all
select 7,2 union all
select 8,3 union all
select 9,4 union all
select 10,5 union all
select 11,6
gocreate function fn_FirstChild(@f int)
returns varchar(300)
as
begin
declare @str varchar(300)
set @str=cast(@f as varchar(10))if exists(select 1 from tb1 where Fatherid =@f)
select top 1 @str=@str+' '+ dbo.fn_FirstChild(ChildId) from tb1 where Fatherid =@freturn @str
end
goselect '0 '+ dbo.fn_FirstChild(ChildId) as result from tb1 where Fatherid =0drop table tb1
drop function fn_FirstChild
/*
result
-------------------------------------
0 1 3 8
0 2 5 10
*/