- SQL code
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[P_DJS](@deeplevel int,@firstcode nvarchar(100))ASSET NOCOUNT ONSET XACT_ABORT ONBEGINcreate 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 [email protected] WHILE @@rowcount > 0 BEGINSET @deeplevel = @deeplevel + 1update #tree set isLeafnode= 0 from #tree join BOMT on [email protected] and BOMT.BOMNO collate database_default =#tree.codeinsert #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 [email protected] and BOMT.BOMNO collate database_default =#tree.code left join MAINBOM on BOMT.CODE=MAINBOM.BOMNOENDselect 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 codeRETURN @@ERROR ENDSET NOCOUNT OFFSET XACT_ABORT OFF想实现这样一个功能在第一条数据的后面加一个字段A，字段A的值是((2807+(2087*(WASTAGE/100)))*BOMT.QTY_NEED)，而它下接数据的字段A的值是它上接数据这个字段A的值做基数来进行计算，就想((上接字段A+(上接字段A*(WASTAGE/100)))*BOMT.QTY_NEED)S
就这样一条一条往下算下接的字段A计算基数是它上接字段A的值，不知道我这样说的明白不？
- SQL code
exec P_DJS 1,'WI-EGD-3575'
- SQL code
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 3 WOK00006300 1.00 3 WOX00000700 1.00 3 WOX00001700 1.00 3 ZB111100400 0.00 3 ZC110200503 0.00 3 ZC110200604 0.00 3 ZC111100101 0.00 3 ZP111100700 1.00 4 MOD00001600 0.25 4 PMA01004900 3.00 4 PMA01004900 3.00 4 PMA01004900 3.00 4 PMA03000100 3.00 4 PMA03000100 3.00 4 PMA05000100 3.00 4 PMA05000100 3.00 4 PMA05000100 3.00 4 PMA05000100 3.00 4 PMA05000100 3.00 4 PMA05000100 3.00 4 PMA05000100 3.00 4 PMA05000100 3.00 4 PMA05000100 3.00 4 PMA05000100 3.00 4 PMA06001100 3.00 4 PMA09000100 3.00 4 PMA09000100 3.00 4 PMA10000400 3.00 4 PMB00006600 3.00 4 PMB00006600 3.00 4 PMB00043400 3.00 4 PMB00053800 3.00 4 PMB00053800 3.00 4 POA00007300 2.00 4 POA00007900 2.00 4 POA00008200 2.00 4 POA00097500 2.00 4 POA00098000 2.00 4 POA00100800 2.00 4 POA03091500 2.00 4 POA07022300 2.00 4 POA21008100 2.00 4 POB01007200 1.00 4 POB01007300 1.00 4 POB14001500 1.00 4 PPA15097100 15.00 4 PPA15097200 15.00 4 PPA15098100 15.00 4 PPA15098700 15.00 4 PPA15139200 15.00 4 PPA15139400 15.00 4 PSA11101700 0.00 4 WBH00000300 1.00 4 WOH00034500 1.00 4 WOO60000700 0.00 4 ZC111100400 0.00 5 COA00005800 3.00 5 COA00005800 3.00 5 COA00005800 3.00 5 COA00005800 3.00 5 COA00005800 3.00 5 COA00005800 3.00 5 MZB01000100 0.75 5 MZB03000100 0.75 5 MZB04005700 0.75 5 MZC01000100 0.75 5 MZC03000100 0.75 5 MZC04000100 0.75 5 MZC06000200 0.75 5 MZC11000100 0.75 5 MZE01002200 0.75 5 MZE02001100 0.75 5 MZE02001200 0.75 5 MZE03000100 0.75 5 MZE04001500 0.75 5 MZE04001600 0.75 5 MZE06000300 0.75 5 MZM01001100 0.75 5 MZM02003000 0.75 5 MZM04000300 0.75 5 MZM14000100 0.75 5 MZM15000100 0.75 5 MZM18000500 0.00 5 MZM19000500 0.75 5 MZM21000300 0.00 5 MZM27001400 0.75 5 MZM27002000 0.75 5 MZM27002400 0.75 5 MZM27002900 0.75 5 MZM29000100 0.75 5 MZP01003500 0.75 5 MZP01003800 0.75 5 MZP02000100 0.75 5 MZP03003800 0.75 5 MZP04001700 0.75 5 MZP14000100 0.75 5 PMA01002000 3.00 5 PMA01002000 3.00 5 PMA01002000 3.00 5 PMA04000100 3.00 5 PMA05000100 3.00 5 PMA05000100 3.00 5 PMA06001100 3.00 5 PMA10000400 3.00 5 PMA13000400 3.00 5 PMB00033500 3.00 5 PMB00060600 3.00 5 POA11101700 2.00 5 POA15097100 2.00 5 POA15097200 2.00 5 POA15098100 2.00 5 POA15098700 2.00 5 POA15139200 2.00 5 POA15139400 2.00 5 WOH00002300 1.00 5 WOH00029500 1.00 5 WOH00029800 1.00 5 WOH00032000 1.00 5 WOH00052300 1.00 6 MZM09000200 0.75 6 MZM09000300 0.75 6 MZM11000200 0.75 6 MZM17000300 0.75 6 MZM21000200 0.75 6 MZM32000500 0.75 6 PMA01004900 3.00 6 PMA01004900 3.00 6 PMA01004900 3.00 6 PMA01004900 3.00 6 PMA01004900 3.00 6 PMA01004900 3.00 6 PMA10000400 3.00 6 PMA13000400 3.00 6 POA00007800 2.00 6 WOH00002100 1.00 6 WOO30002200 0.00 6 WOO32000200 0.00 6 WOO40003700 0.00 6 WOO40004900 0.00 6 WOO40006900 0.00 6 WOO40007200 0.00 7 PMA05000100 3.00