好贷网好贷款

存储过程实现一个功能 40zhangzhen_927116]

发布时间:2016-12-5 4:33:41 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"存储过程实现一个功能 40zhangzhen_927116]",主要涉及到存储过程实现一个功能 40zhangzhen_927116]方面的内容,对于存储过程实现一个功能 40zhangzhen_927116]感兴趣的同学可以参考一下。

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 [email protected]  WHILE @@rowcount > 0  BEGIN SET @deeplevel = @deeplevel + 1 update #tree set isLeafnode= 0 from #tree      join BOMT     on [email protected]     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 [email protected]     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     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

层次        code           QTY_NEED      WASTAGE 1 KJ-1111-003 1.00000000 0.00   2 ZC111100701 1.00000000 0.00   2 ZA111100102 1.00000000 0.00   2 ZA111100601 1.00000000 0.00   2 ZA111100502 1.00000000 0.00   2 ZA111100403 1.00000000 0.00     3 DDD03019800 1.00000000 0.25     3 ZC111100101 1.00000000 0.00     3 EOJ00016400 1.00000000 1.00     3 PCA00100800 2.00000000 10.00     3 PPS15098700 1.00000000 0.00     3 JOS00012100 2.00000000 1.00     3 JOS00002900 5.00000000 1.00     3 PCA00098000 1.00000000 5.00     3 PCA00097500 1.00000000 5.00     3 PPS15097200 1.00000000 0.00     3 PPS15097100 1.00000000 0.00     3 PPS15098100 1.00000000 0.00     3 POA10022200 1.00000000 2.00     3 JOA01005700 1.00000000 1.00     3 PSA07022300 1.00000000 0.00     3 WOK00006300 2.00000000 1.00     3 ZP111100700 1.00000000 1.00     3 POA01097800 1.00000000 2.00     3 POA01097900 1.00000000 2.00     3 EOJ00002000 1.00000000 1.00     3 POB00013500 2.00000000 1.00     3 WOH00052600 1.00000000 1.00     3 POA01097300 1.00000000 2.00     3 JOA01000300 1.00000000 1.00     3 POA01097400 1.00000000 2.00     3 POA15098200 1.00000000 2.00     3 POB00120300 1.00000000 1.00     3 WOH00050800 1.00000000 1.00     3 ZB111100400 1.00000000 0.00     3 WOH00029900 1.00000000 1.00     3 JOS00014900 2.00000000 1.00     3 WBL00034500 1.00000000 1.00     3 POB02001600 1.00000000 1.00     3 POB00013500 2.00000000 1.00     3 POA00007600 1.00000000 2.00     3 WOX00000700 1.00000000 1.00     3 POA00007200 1.00000000 2.00     3 POA00008300 1.00000000 2.00     3 POA00007100 1.00000000 2.00     3 POA01151900 1.00000000 2.00     3 POA00007700 1.00000000 2.00     3 POA00008400 1.00000000 2.00     3 PPS15139200 1.00000000 0.00     3 POA00102300 1.00000000 2.00     3 POA00008700 3.00000000 2.00     3 ZC110200604 1.00000000 0.00     3 WAA00001300 1.00000000 1.00     3 ZC110200503 1.00000000 0.00     3 JOA01000100 1.00000000 1.00     3 WOH00002400 1.00000000 1.00     3 POB00013500 2.00000000 1.00     3 PPS15139400 1.00000000 0.00     3 EOJ00001400 1.00000000 1.00     3 WOH00052500 1.00000000 1.00     3 WOX00001700 1.00000000 1.00     3 COD00000600 0.00260000 3.00     3 COD00000300 0.00010000 3.00     3 COD00000600 0.00310000 3.00     3 WOH00004400 1.00000000 1.00     3 COD00000300 0.00010000 3.00     3 POA15139300 1.00000000 2.00     3 WBH00001500 3.00000000 1.00     3 POA00076500 1.00000000 2.00     3 POA00076400 1.00000000 2.00     3 POA00076600 1.00000000 2.00       4 MOD00001600 1.00000000 0.25       4 PMA01004900 0.00805000 3.00       4 PMA01004900 0.04780000 3.00       4 PMA01004900 0.01100000 3.00       4 PMA03000100 0.00418000 3.00       4 PMA03000100 0.00418000 3.00       4 PMA05000100 0.00775000 3.00       4 PMA05000100 0.00137000 3.00       4 PMA05000100 0.00240000 3.00       4 PMA05000100 0.00243000 3.00       4 PMA05000100 0.00343000 3.00       4 PMA05000100 0.00707000 3.00       4 PMA05000100 0.00620000 3.00       4 PMA05000100 0.00302000 3.00       4 PMA05000100 0.00422000 3.00       4 PMA05000100 0.00142000 3.00       4 PMA06001100 0.01180000 3.00       4 PMA09000100 0.02865000 3.00       4 PMA09000100 0.02650000 3.00       4 PMA10000400 0.00450000 3.00       4 PMB00006600 0.00016720 3.00       4 PMB00006600 0.00016720 3.00       4 PMB00043400 0.00047200 3.00       4 PMB00053800 0.00114600 3.00       4 PMB00053800 0.00106000 3.00       4 POA00007300 1.00000000 2.00       4 POA00007900 1.00000000 2.00       4 POA00008200 1.00000000 2.00       4 POA00097500 1.00000000 2.00       4 POA00098000 1.00000000 2.00       4 POA00100800 1.00000000 2.00       4 POA03091500 1.00000000 2.00       4 POA07022300 1.00000000 2.00       4 POA21008100 1.00000000 2.00       4 POB01007200 1.00000000 1.00       4 POB01007300 2.00000000 1.00       4 POB14001500 3.00000000 1.00       4 PPA15097100 1.00000000 15.00       4 PPA15097200 1.00000000 15.00       4 PPA15098100 1.00000000 15.00       4 PPA15098700 1.00000000 15.00       4 PPA15139200 1.00000000 15.00       4 PPA15139400 1.00000000 15.00       4 PSA11101700 1.00000000 0.00       4 WBH00000300 1.00000000 1.00       4 WOH00034500 1.00000000 1.00       4 WOO60000700 0.00646000 0.00       4 ZC111100400 1.00000000 0.00         5 COA00005800 0.00333000 3.00         5 COA00005800 0.00615000 3.00         5 COA00005800 0.00100000 3.00         5 COA00005800 0.00800000 3.00         5 COA00005800 0.00615000 3.00         5 COA00005800 0.00615000 3.00         5 MZB01000100 0.10000000 0.75         5 MZB03000100 0.04000000 0.75         5 MZB04005700 1.00000000 0.75         5 MZC01000100 0.00200000 0.75         5 MZC03000100 0.00002500 0.75         5 MZC04000100 0.00180000 0.75         5 MZC06000200 0.00600000 0.75         5 MZC11000100 0.00100000 0.75         5 MZE01002200 0.04700000 0.75         5 MZE02001100 1.00000000 0.75         5 MZE02001200 1.00000000 0.75         5 MZE03000100 2.00000000 0.75         5 MZE04001500 2.00000000 0.75         5 MZE04001600 1.00000000 0.75         5 MZE06000300 2.00000000 0.75         5 MZM01001100 1.00000000 0.75         5 MZM02003000 1.00000000 0.75         5 MZM04000300 1.00000000 0.75         5 MZM14000100 2.00000000 0.75         5 MZM15000100 2.00000000 0.75         5 MZM18000500 1.00000000 0.00         5 MZM19000500 1.00000000 0.75         5 MZM21000300 1.00000000 0.00         5 MZM27001400 3.00000000 0.75         5 MZM27002000 3.00000000 0.75         5 MZM27002400 1.00000000 0.75         5 MZM27002900 2.00000000 0.75         5 MZM29000100 1.00000000 0.75         5 MZP01003500 1.00000000 0.75         5 MZP01003800 1.00000000 0.75         5 MZP02000100 1.00000000 0.75         5 MZP03003800 0.00300000 0.75         5 MZP04001700 0.00126000 0.75         5 MZP14000100 0.06000000 0.75         5 PMA01002000 0.00815000 3.00         5 PMA01002000 0.00325000 3.00         5 PMA01002000 0.00228000 3.00         5 PMA04000100 0.03320000 3.00         5 PMA05000100 0.00165000 3.00         5 PMA05000100 0.01325000 3.00         5 PMA06001100 0.00515000 3.00         5 PMA10000400 0.00653000 3.00         5 PMA13000400 0.16240000 3.00         5 PMB00033500 0.00026120 3.00         5 PMB00060600 0.00132800 3.00         5 POA11101700 1.00000000 2.00         5 POA15097100 1.00000000 2.00         5 POA15097200 1.00000000 2.00         5 POA15098100 1.00000000 2.00         5 POA15098700 1.00000000 2.00         5 POA15139200 1.00000000 2.00         5 POA15139400 1.00000000 2.00         5 WOH00002300 1.00000000 1.00         5 WOH00029500 1.00000000 1.00         5 WOH00029800 1.00000000 1.00         5 WOH00032000 1.00000000 1.00         5 WOH00052300 1.00000000 1.00           6 MZM09000200 1.00000000 0.75           6 MZM09000300 1.00000000 0.75           6 MZM11000200 1.00000000 0.75           6 MZM17000300 1.00000000 0.75           6 MZM21000200 1.00000000 0.75           6 MZM32000500 1.00000000 0.75           6 PMA01004900 0.04600000 3.00           6 PMA01004900 0.04100000 3.00           6 PMA01004900 0.02225000 3.00           6 PMA01004900 0.00330000 3.00           6 PMA01004900 0.03090000 3.00           6 PMA01004900 0.08125000 3.00           6 PMA10000400 0.00860000 3.00           6 PMA13000400 0.12785000 3.00           6 POA00007800 1.00000000 2.00           6 WOH00002100 1.00000000 1.00           6 WOO30002200 0.00122000 0.00           6 WOO32000200 0.00159000 0.00           6 WOO40003700 0.04028000 0.00           6 WOO40004900 0.08249000 0.00           6 WOO40006900 0.04800000 0.00           6 WOO40007200 0.04910000 0.00             7 PMA05000100 0.00131000 3.00

不明白。

木有看懂

引用 2 楼 ssp2009 的回复:不明白。 打个比喻, 有字段 CODE,WASTAGE,BOMT.QTY_NEED  有数据a b c,他的子节点是d e f,d e f的子节点是 1 2 3 我现在想在 abc 后面加个字段,而这个字段的值是计算出来的结果公式是((2807+(2087*(WASTAGE/100)))*BOMT.QTY_NEED),那么a b c的子节点d e f的这个字段也是计算出来但是公式里面的2807变成了a b c新增字段的值。 

不知道我上面说的清楚不

上一篇:MS SQL SERVER 2008 R2 性能 多服务器 数据收集 仪表盘 200lcw321321]
下一篇:sqlserver 2005导出数据出现异常 22bisubisu]

相关文章

相关评论