set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[P_DJS]
(
@deeplevel int,
@firstcode nvarchar(100)
--@liqty int
)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN
create table #tree
(
bomno nvarchar(100),
code nvarchar(100),
deeplevel int,
cbdesc nvarchar(100),
qty_nee numeric(19,8),
loc varchar(32),
wastage numeric(6,2),
--liqty numeric(6,2),
isLeafnode int,
tree nvarchar(max) default ''
)
declare
@cbdesc varchar(32),
@QTY_NEED numeric(19,8),
@loc varchar(32),
@wastage numeric(6,2)
insert #tree
select BOMT.BOMNO,BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,1,
BOMT.CODE + left('00000000000000000000',20-len(BOMT.CODE)) from BOMT left JOIN MAINBOM on BOMT.CODE=MAINBOM.BOMNO where BOMT.BOMNO=@firstcode
WHILE @@rowcount > 0
BEGIN
SET @deeplevel = @deeplevel + 1update #tree set isLeafnode= 0 from #tree
join BOMT
on #tree.deeplevel=@deeplevel-1
and BOMT.BOMNO collate database_default =#tree.code
insert #tree
select @firstcode,BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,1,#tree.tree+'_'+BOMT.CODE+left('00000000000000000000',20-len(BOMT.CODE))
from BOMT
join #tree
on #tree.deeplevel=@deeplevel-1
and BOMT.BOMNO collate database_default =#tree.code
left join MAINBOM
on BOMT.CODE=MAINBOM.BOMNO
END
select space((deeplevel-1)*2)+cast(deeplevel as varchar),code,cbdesc,qty_nee,loc,wastage,(select top 1 TAXUP from ANT where ANT.CODE=#tree.code AND TAXUP > 0 order by DATETIME desc) taxup from #tree
--order by code
RETURN @@ERROR ENDSET NOCOUNT OFF
SET XACT_ABORT OFF这是取出所有子节点跟父节点的数据,现在要在它的结果上面再加上一字段A,而A字段的计算方式是:(2087+(2087*(WASTAGE/100)))*QTY_NEED,所有子节点的计算公式里面的2087都是取它父节点的字段A值进行计算的。求要这样怎么修改。
打个比喻:
a b c
1 2 3
2 5 6
5 8 9
3 7 6给一个数字4那么我要得到
a b c d
1 2 3 (4+(4*(2/100)))*3) 这个d值我先把他用H表示
2 5 6 (h+(h*(5/100)))*6) 这个d值我先把他用f表示
5 8 9 (f+(f*(8/100)))*9)
不知道我这样说能不能明白,不明白可以提出来。我会再加解释。
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[P_DJS]
(
@deeplevel int,
@firstcode nvarchar(100)
--@liqty int
)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN
create table #tree
(
bomno nvarchar(100),
code nvarchar(100),
deeplevel int,
cbdesc nvarchar(100),
qty_nee numeric(19,8),
loc varchar(32),
wastage numeric(6,2),
--liqty numeric(6,2),
isLeafnode int,
tree nvarchar(max) default ''
)
declare
@cbdesc varchar(32),
@QTY_NEED numeric(19,8),
@loc varchar(32),
@wastage numeric(6,2)
insert #tree
select BOMT.BOMNO,BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,1,
BOMT.CODE + left('00000000000000000000',20-len(BOMT.CODE)) from BOMT left JOIN MAINBOM on BOMT.CODE=MAINBOM.BOMNO where BOMT.BOMNO=@firstcode
WHILE @@rowcount > 0
BEGIN
SET @deeplevel = @deeplevel + 1update #tree set isLeafnode= 0 from #tree
join BOMT
on #tree.deeplevel=@deeplevel-1
and BOMT.BOMNO collate database_default =#tree.code
insert #tree
select @firstcode,BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,1,#tree.tree+'_'+BOMT.CODE+left('00000000000000000000',20-len(BOMT.CODE))
from BOMT
join #tree
on #tree.deeplevel=@deeplevel-1
and BOMT.BOMNO collate database_default =#tree.code
left join MAINBOM
on BOMT.CODE=MAINBOM.BOMNO
END
select space((deeplevel-1)*2)+cast(deeplevel as varchar),code,cbdesc,qty_nee,loc,wastage,(select top 1 TAXUP from ANT where ANT.CODE=#tree.code AND TAXUP > 0 order by DATETIME desc) taxup from #tree
--order by code
RETURN @@ERROR ENDSET NOCOUNT OFF
SET XACT_ABORT OFF这是取出所有子节点跟父节点的数据,现在要在它的结果上面再加上一字段A,而A字段的计算方式是:(2087+(2087*(WASTAGE/100)))*QTY_NEED,所有子节点的计算公式里面的2087都是取它父节点的字段A值进行计算的。求要这样怎么修改。
打个比喻:
a b c
1 2 3
2 5 6
5 8 9
3 7 6给一个数字4那么我要得到
a b c d
1 2 3 (4+(4*(2/100)))*3) 这个d值我先把他用H表示
2 5 6 (h+(h*(5/100)))*6) 这个d值我先把他用f表示
5 8 9 (f+(f*(8/100)))*9)
不知道我这样说能不能明白,不明白可以提出来。我会再加解释。
SELECT (3061+(3061*(0.02)))*1
SELECT (3061+(3061*(2/100)))*1
他们两个结果怎么会不一样?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[P_DJS]
(
@deeplevel int,
@firstcode nvarchar(100),
@liqty int
)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN
create table #tree
(
bomno nvarchar(100),
code nvarchar(100),
deeplevel int,
cbdesc nvarchar(100),
qty_nee numeric(19,8),
loc varchar(32),
wastage numeric(6,2),
liqty numeric(19,8),
isLeafnode int,
tree nvarchar(max) default ''
)
declare
@cbdesc varchar(32),
@QTY_NEED numeric(19,8),
@loc varchar(32),
@wastage numeric(6,2)insert #tree
select BOMT.BOMNO,BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,ceiling((@liqty+(@liqty*(WASTAGE/100)))*QTY_NEED),1,
BOMT.CODE + left('00000000000000000000',20-len(BOMT.CODE)) from BOMT left JOIN MAINBOM on BOMT.CODE=MAINBOM.BOMNO where BOMT.BOMNO=@firstcode
WHILE @@rowcount > 0
BEGIN
SET @deeplevel = @deeplevel + 1update #tree set isLeafnode= 0 from #tree
join BOMT
on #tree.deeplevel=@deeplevel-1
and BOMT.BOMNO collate database_default =#tree.code
insert #tree
select @firstcode,BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,ceiling((liqty+(@liqty*(WASTAGE/100)))*QTY_NEED),1,#tree.tree+'_'+BOMT.CODE+left('00000000000000000000',20-len(BOMT.CODE))
from BOMT
join #tree
on #tree.deeplevel=@deeplevel-1
and BOMT.BOMNO collate database_default =#tree.code
left join MAINBOM
on BOMT.CODE=MAINBOM.BOMNO
END
select space((deeplevel-1)*2)+cast(deeplevel as varchar),code,cbdesc,qty_nee,loc,wastage,liqty,(select top 1 TAXUP from ANT where ANT.CODE=#tree.code AND TAXUP > 0 order by DATETIME desc) taxup from #tree
--order by code
RETURN @@ERROR ENDSET NOCOUNT OFF
SET XACT_ABORT OFF