存储过程实现一个效能
存储过程实现一个功能
想实现这样一个功能在第一条数据的后面加一个字段A，字段A的值是((2807+(2087*(WASTAGE/100)))*BOMT.QTY_NEED)，而它下接数据的字段A的值是它上接数据这个字段A的值做基数来进行计算，就想((上接字段A+(上接字段A*(WASTAGE/100)))*BOMT.QTY_NEED)S
就这样一条一条往下算下接的字段A计算基数是它上接字段A的值，不知道我这样说的明白不？
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[P_DJS]
(
@deeplevel int,
@firstcode nvarchar(100)
)
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),
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 + 1
update #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 END
SET NOCOUNT OFF
SET XACT_ABORT OFF
想实现这样一个功能在第一条数据的后面加一个字段A，字段A的值是((2807+(2087*(WASTAGE/100)))*BOMT.QTY_NEED)，而它下接数据的字段A的值是它上接数据这个字段A的值做基数来进行计算，就想((上接字段A+(上接字段A*(WASTAGE/100)))*BOMT.QTY_NEED)S
就这样一条一条往下算下接的字段A计算基数是它上接字段A的值，不知道我这样说的明白不？
exec P_DJS 1,'WI-EGD-3575'
1 KJ-1111-003 0.00
2 ZA111100102 0.00
2 ZA111100403 0.00
2 ZA111100502 0.00
2 ZA111100601 0.00
2 ZC111100701 0.00
3 COD00000300 3.00
3 COD00000300 3.00
3 COD00000600 3.00
3 COD00000600 3.00
3 DDD03019800 0.25
3 EOJ00001400 1.00
3 EOJ00002000 1.00
3 EOJ00016400 1.00
3 JOA01000100 1.00
3 JOA01000300 1.00
3 JOA01005700 1.00
3 JOS00002900 1.00
3 JOS00012100 1.00
3 JOS00014900 1.00
3 PCA00097500 5.00
3 PCA00098000 5.00
3 PCA00100800 10.00
3 POA00007100 2.00
3 POA00007200 2.00
3 POA00007600 2.00
3 POA00007700 2.00
3 POA00008300 2.00
3 POA00008400 2.00
3 POA00008700 2.00
3 POA00076400 2.00
3 POA00076500 2.00
3 POA00076600 2.00
3 POA00102300 2.00
3 POA01097300 2.00
3 POA01097400 2.00
3 POA01097800 2.00
3 POA01097900 2.00
3 POA01151900 2.00
3 POA10022200 2.00
3 POA15098200 2.00
3 POA15139300 2.00
3 POB00013500 1.00
3 POB00013500 1.00
3 POB00013500 1.00
3 POB00120300 1.00
3 POB02001600 1.00
3 PPS15097100 0.00
3 PPS15097200 0.00
3 PPS15098100 0.00
3 PPS15098700 0.00
3 PPS15139200 0.00
3 PPS15139400 0.00
3 PSA07022300 0.00
3 WAA00001300 1.00
3 WBH00001500 1.00
3 WBL00034500 1.00
3 WOH00002400 1.00
3 WOH00004400 1.00
3 WOH00029900 1.00
3 WOH00050800 1.00
3 WOH00052500 1.00
3 WOH00052600 1.00