CREATE TABLE DBO.filiation (
[subindex] [smallint]not null default 0,--
[Id] [nvarchar](20)not null default'',--物料编号
[fatherId] [nvarchar](20)not null default'',--父物料编号
[dosage] [decimal](9,5)default 0,--单位用量
[wastage] [decimal](9,4)default 0,--单位损耗率
PRIMARY KEY (Id,FatherId))insert into filiation
select 0,'0','',0,0
union all select 1,'11021L','7652ES.061',1,0
union all select 2,'16121L','7652ES.061',1,0
union all select 1,'2125L','7651D3',1,0,
union all select 1,'4240','11021L',1,0
union all select 3,'7651D3','0',1,0
union all select 3,'7651D3','7652ES.061',1,0
union all select 1,'7651D4','2125L',1,0
union all select 1,'7652ES.061','0',1,0
union all select 2,'CARTON7652.058','0',1,0
union all select 1,'CARTON7652.058','4240',1,0
union all select 1,'F318.061','7651D4',1,0哪位高手能否照着
http://topic.csdn.net/t/20060216/16/4559959.html
中zjcxc(邹建)的例子一样写个类似 f_bom的函数,来实现查询BOM表(filiation)中遍历,类似
/*--测试结果
层次 item brand part_no qty
-------------- ---------- ---------- ---------- -------
├─ 1 A A1 0
├─ 2 B AAAAA 1
├─ 6 C AAADSFD 1
├─ 7 D D22 1
├─ 8 C DDDD512 1
├─ 9 A AA3223 1
├─ 10 DD 356 1
├─ 6 C AAADSFD 2
├─ 7 D D22 1
├─ 8 C DDDD512 1
├─ 9 A AA3223 1
├─ 10 DD 356 1
的结果,
谢谢了.
[subindex] [smallint]not null default 0,--
[Id] [nvarchar](20)not null default'',--物料编号
[fatherId] [nvarchar](20)not null default'',--父物料编号
[dosage] [decimal](9,5)default 0,--单位用量
[wastage] [decimal](9,4)default 0,--单位损耗率
PRIMARY KEY (Id,FatherId))insert into filiation
select 0,'0','',0,0
union all select 1,'11021L','7652ES.061',1,0
union all select 2,'16121L','7652ES.061',1,0
union all select 1,'2125L','7651D3',1,0,
union all select 1,'4240','11021L',1,0
union all select 3,'7651D3','0',1,0
union all select 3,'7651D3','7652ES.061',1,0
union all select 1,'7651D4','2125L',1,0
union all select 1,'7652ES.061','0',1,0
union all select 2,'CARTON7652.058','0',1,0
union all select 1,'CARTON7652.058','4240',1,0
union all select 1,'F318.061','7651D4',1,0哪位高手能否照着
http://topic.csdn.net/t/20060216/16/4559959.html
中zjcxc(邹建)的例子一样写个类似 f_bom的函数,来实现查询BOM表(filiation)中遍历,类似
/*--测试结果
层次 item brand part_no qty
-------------- ---------- ---------- ---------- -------
├─ 1 A A1 0
├─ 2 B AAAAA 1
├─ 6 C AAADSFD 1
├─ 7 D D22 1
├─ 8 C DDDD512 1
├─ 9 A AA3223 1
├─ 10 DD 356 1
├─ 6 C AAADSFD 2
├─ 7 D D22 1
├─ 8 C DDDD512 1
├─ 9 A AA3223 1
├─ 10 DD 356 1
的结果,
谢谢了.
第4楼第1个实例的测试效果,算深度排序吧?
[subindex] [smallint]not null default 0,--
[Id] [nvarchar](20)not null default'',--物料编号
[fatherId] [nvarchar](20)not null default'',--父物料编号
[dosage] [decimal](9,5)default 0,--单位用量
[wastage] [decimal](9,4)default 0,--单位损耗率
PRIMARY KEY (Id,FatherId))insert into filiation
select 0,'0','',0,0
union all select 1,'11021L','7652ES.061',1,0
union all select 2,'16121L','7652ES.061',1,0
union all select 1,'2125L','7651D3',1,0
union all select 1,'4240','11021L',1,0
union all select 3,'7651D3','0',1,0
union all select 3,'7651D3','7652ES.061',1,0
union all select 1,'7651D4','2125L',1,0
union all select 1,'7652ES.061','0',1,0
union all select 2,'CARTON7652.058','0',1,0
union all select 1,'CARTON7652.058','4240',1,0
union all select 1,'F318.061','7651D4',1,0select * from filiation--展开bom查询的函数
create function fn_bom(
@id [nvarchar](20)
)returns @r table(
subindex [smallint],
id nvarchar(20),
fatherid nvarchar(20),
dosage dec(9,5),
wastage dec(9,4),
level int,--层次
sid varchar(8000)--排序字段,通过这个来排序,可以体现出树形的层次
)
as
begin
declare @l int
set @l=0
insert @r select subindex,@id,fatherid,dosage,[wastage],@l,id
from filiation
where id=@id
while @@rowcount>0
begin
set @l=@l+1
insert @r select i.subindex,i.id,i.fatherid,i.dosage,i.[wastage],@l,r.sid+','+i.id
from filiation i,@r r
where r.level=@l-1
and r.id=i.fatherid
end
return
end
go select lvl=replicate(' ',level)+'├─',subindex,ID,fatherid,dosage,wastage from fn_bom(0) order by sid
/*
lvl subindex ID fatherid dosage wastage
├─ 0 0 0.00000 0.0000
├─ 3 7651D3 0 1.00000 0.0000
├─ 1 2125L 7651D3 1.00000 0.0000
├─ 1 7651D4 2125L 1.00000 0.0000
├─ 1 F318.061 7651D4 1.00000 0.0000
├─ 1 7652ES.061 0 1.00000 0.0000
├─ 1 11021L 7652ES.061 1.00000 0.0000
├─ 1 4240 11021L 1.00000 0.0000
├─ 1 CARTON7652.058 4240 1.00000 0.0000
├─ 2 16121L 7652ES.061 1.00000 0.0000
├─ 3 7651D3 7652ES.061 1.00000 0.0000
├─ 1 2125L 7651D3 1.00000 0.0000
├─ 1 7651D4 2125L 1.00000 0.0000
├─ 1 F318.061 7651D4 1.00000 0.0000
├─ 2 CARTON7652.058 0 1.00000 0.0000
*/
--SQL2005展开bom查询的函数
create function fn_bom2005(
@id [nvarchar](20)
)returns table
as
return(
with t as
(
select *,lvl=0,sid=cast(id as varchar(max))
from filiation
where id=@id
union all
select a.*,lvl+1,cast(t.sid+','+a.id as varchar(max))
from filiation a,t
where a.fatherid=t.id
)
select lvl=replicate(' ',lvl)+'├─',subindex,ID,fatherid,dosage,wastage,sid
from t
)
go select lvl,subindex,ID,fatherid,dosage,wastage from fn_bom2005(0) order by sid
/*
lvl subindex ID fatherid dosage wastage
├─ 0 0 0.00000 0.0000
├─ 3 7651D3 0 1.00000 0.0000
├─ 1 2125L 7651D3 1.00000 0.0000
├─ 1 7651D4 2125L 1.00000 0.0000
├─ 1 F318.061 7651D4 1.00000 0.0000
├─ 1 7652ES.061 0 1.00000 0.0000
├─ 1 11021L 7652ES.061 1.00000 0.0000
├─ 1 4240 11021L 1.00000 0.0000
├─ 1 CARTON7652.058 4240 1.00000 0.0000
├─ 2 16121L 7652ES.061 1.00000 0.0000
├─ 3 7651D3 7652ES.061 1.00000 0.0000
├─ 1 2125L 7651D3 1.00000 0.0000
├─ 1 7651D4 2125L 1.00000 0.0000
├─ 1 F318.061 7651D4 1.00000 0.0000
├─ 2 CARTON7652.058 0 1.00000 0.0000
*/