表一:
MF_BOM
bom_no id_no cst_make
1000000001-> 101000001-> 44.00000000
101000001-> 2060001-> 3.00000000
2060001-> 2060002-> 11.00000000
2060001-> 2060003-> 22.00000000
2060001-> 2060004-> 33.00000000
表二:
MF_BOM1
BOM_NO CST_MAKE
1000000001-> 44.00000000
101000001-> 3.00000000
2060001-> 66.00000000
表1与表2可以看出:
1000000001-> 下一层有 101000001->
而101000001->下一层有2060001->
而2060001->下层有 2060002->和2060003->和2060004->
组成。
那么1000000001->的费用字段包含下面之和。 求1000000001->的cst_make值?如何归集上去?
MF_BOM
bom_no id_no cst_make
1000000001-> 101000001-> 44.00000000
101000001-> 2060001-> 3.00000000
2060001-> 2060002-> 11.00000000
2060001-> 2060003-> 22.00000000
2060001-> 2060004-> 33.00000000
表二:
MF_BOM1
BOM_NO CST_MAKE
1000000001-> 44.00000000
101000001-> 3.00000000
2060001-> 66.00000000
表1与表2可以看出:
1000000001-> 下一层有 101000001->
而101000001->下一层有2060001->
而2060001->下层有 2060002->和2060003->和2060004->
组成。
那么1000000001->的费用字段包含下面之和。 求1000000001->的cst_make值?如何归集上去?
(
select * from [MF_BOM] WHERE bom_no='1000000001'
UNION ALL
SELECT a.* FROM mf_bom AS a INNER JOIN f AS b ON a.bom_no=b.id_no
)SELECT SUM(cst_make) FROM f AS a INNER JOIN MF_BOM1 AS b ON a.BOM_NO=b.BOM_NO WHERE a.bom_no='1000000001'
as
(
select 1000000001,101000001,44.00000000
union all select 101000001,2060001,3.00000000
union all select 2060001,2060002,11.00000000
union all select 2060001,2060003,22.00000000
union all select 2060001,2060004,33.00000000
),tt
as
(
select t.bom_no,tt.id_no,isnull(tt.cst_make,0) as cst_make
from MF_BOM t
left join MF_BOM tt
on t.bom_no = tt.id_no
),t
as
(
select bom_no,id_no,bom_no as bom_no_t,cast(cst_make as numeric(20,8)) as cst_make,1 as level
from tt
where id_no is nullunion allselect t.bom_no,t.id_no,b.id_no,cast(b.CST_MAKE as numeric(20,8)),level + 1
from t
inner join MF_BOM b
on t.bom_no_t = b.bom_no
)select sum(cst_make)
from t
where bom_no = 1000000001
/*
113.00000000
*/
;with MF_BOM(bom_no,id_no,cst_make)
as
(
select 1000000001,101000001,44.00000000
union all select 101000001,2060001,3.00000000
union all select 2060001,2060002,11.00000000
union all select 2060001,2060003,22.00000000
union all select 2060001,2060004,33.00000000
),t
as
(
select bom_no,id_no,bom_no as bom_no_t,cast(0 as numeric(20,8)) as cst_make,1 as level
from MF_BOM
where bom_no= 1000000001 --查询条件union allselect t.bom_no,t.id_no,b.id_no,cast(b.CST_MAKE as numeric(20,8)),level + 1
from t
inner join MF_BOM b
on t.bom_no_t = b.bom_no
)select sum(cst_make)
from t
/*
113.00000000
*/
create table MF_BOM
(bom_no varchar(15),id_no varchar(15),cst_make int)insert into MF_BOM
select '1000000001','101000001',44 union all
select '101000001','2060001',3 union all
select '2060001','2060002',11 union all
select '2060001','2060003',22 union all
select '2060001','2060004',33
declare @x varchar(10)
select @x='1000000001';with t as
(select bom_no,id_no,cst_make from MF_BOM where bom_no=@x
union all
select a.bom_no,a.id_no,a.cst_make
from MF_BOM a
inner join t b on a.bom_no=b.id_no)
select @x 'bom_no',
sum(cst_make) 'cst_make'
from t/*
bom_no cst_make
---------- -----------
1000000001 113(1 row(s) affected)
*/