--增加分类 GO IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=32) insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description) Values(32, 'SC32', '门诊医师工作站', 17, 0, 31, 0, Null) else print 'SYS_ScriptCategories.id=32 已经存在.' GO --删除旧记录 delete from SYS_Scripts where id=7485 --删除脚本内容 GO GO IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=7485) insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode) Values(7485, 32, 'Sql_门诊发送医嘱计价', '', '门诊发送医嘱窗口，提取对应计价信息', 1, 1, Null, 1, 'Declare @aDate DateTime declare @lVAF06 int ,@lBCE01A int set @lVAF06 = %d set @lBCE01A = %d Set @aDate = Convert(varchar(10),GetDate(),21) +'' 23:59:59.999'' IF Object_id(''tempdb..#kbmVBDp'') IS NOT NULL DROP TABLE #kbmVBDp IF Object_id(''tempdb..#kbmVAFsd'') IS NOT NULL DROP TABLE #kbmVAFsd IF Object_id(''tempdb..#tmpVBD'') IS NOT NULL DROP TABLE #tmpVBD IF Object_id(''tempdb..#kbmDpt'') IS NOT NULL DROP TABLE #kbmDpt IF Object_id(''tempdb..#kbmbdu'') IS NOT NULL DROP TABLE #kbmbdu CREATE TABLE #kbmVBDp( id int IDENTITY(1,1), NVAF22 varchar(200), --医嘱 BDA02 varchar(20),--类别 VAF22 varchar(128),--收费项目 VAF21 numeric(18,4),--数量 BCK03 varchar(64), AAS02 varchar(32), BBY08 varchar(20), FBDU06 varchar(10), FBDU10 varchar(64), BDU06 tinyint, --从项 BDU05 tinyint, --是否固定项目 lPrice numeric(18,4), BDU07 tinyint, BDU10 tinyint, BCK01 int, VAF01 int, BBY01 int, VAFID int, lMoney numeric(18,4) default(0), lSum numeric(18,4) default(0), lVAFIDSum numeric(18,4) default(0), BDA01 varchar(2), A_Price numeric(18,4), B_Price numeric(18,4), max_Price numeric(18,4), min_Price numeric(18,4), BBY19 tinyint, DSK01 int, BAG23 numeric(18,4), VBD12 numeric(18,4),BBY01A int ) Select VAF01,VAF01A,BBY01,BBX01,BDA01,VAF06,VAF14,VAF15,VAF21,BCK01B,BCK01A,BCK01D,VAF32,VAF22 ,ROWNR,VAF01 as VAFID,DSK01,Crypt INTO #kbmVAFsd From VAF1 Where VAF04 = 1 and VAF06 = @lVAF06 and (BCE01A = @lBCE01A or BCE01G = @lBCE01A) and VAF10 = 1 And BDA01 <> ''3'' UNION ALL Select VAF01,VAF01A,BBY01,BBX01,BDA01,VAF06,VAF14,VAF15,VAF20,BCK01B,BCK01A,BCK01D,VAF32,VAF22 ,ROWNR,VAF01 as VAFID,DSK01,Crypt From VAF1 Where VAF04 = 1 and VAF06 = @lVAF06 and (BCE01A = @lBCE01A or BCE01G = @lBCE01A) and VAF10 = 1 And BDA01 = ''3'' update #kbmVAFsd set VAFID = VAF01A where BDA01 >= ''3'' and VAF01A > 0 update a set a.VAFID = b.VAF01 from #kbmVAFsd a join (select VAF01,VAF01A from #kbmVAFsd where BDA01 <= ''2'' and ROWNR = 1) b on b.VAF01A = a.VAF01 where a.BDA01 = ''T'' and a.VAF32 = 1 select a.BDU04,a.BDU05,a.BDU06,a.BDU07,a.BDU08,a.BDU09,a.BDU10,c.BBY01,a.BBX01,c.BBY25,c.BDN01,c.BBY19 ,c.BCH01,c.AAS01,c.BBY05,c.BBY08,c.BBY27,c.BBY28,0 BBY01A into #kbmbdu from BDU1 a join BBY1 c on c.BBY01 = a.BBY01 where c.BBY34 <> 2 and c.BBY31 > @aDate and exists(select * from #kbmVAFsd b where b.BDA01 >= ''8'' and b.BDA01 <> ''M'' and a.BBX01 = b.BBX01) union all select a.BDU04*d.BCR04,a.BDU05,a.BDU06,a.BDU07,a.BDU08,a.BDU09,a.BDU10,e.BBY01,a.BBX01 ,Case when d.BCR10 = 1 then e.BBY25 else d.BCR07 end BBY25,e.BDN01,e.BBY19,e.BCH01,e.AAS01 ,e.BBY05,e.BBY08,e.BBY27,e.BBY28,d.BBY01A from BDU1 a join BBY1 c on c.BBY01 = a.BBY01 join BCR1 d on d.BBY01A = c.BBY01 join BBY1 e on e.BBY01 = d.BBY01B where c.BBY34 = 2 and c.BBY31 > @aDate and e.BBY31 > @aDate and d.BCR09 > @aDate and exists(select * from #kbmVAFsd b where b.BDA01 >= ''8'' and b.BDA01 <> ''M'' and a.BBX01 = b.BBX01) select a2.BBX05 NVAF22, c.BDA02,b.BBY05 VAF22,a.VAF21,d.BCK03,e.AAS02,b2.BDG02A BBY08,''否'' AS FBDU06 , '''' AS FBDU10,0 as BDU06,1 as BDU05,b.BBY25 lPrice,1 BDU07,0 BDU10,a.BCK01B BCK01,a.VAF01,b.BBY01,a.VAFID ,a.BDA01,b.BBY25 A_Price,b.BBY25 B_price,isnull(b.BBY28,0) max_Price,isnull(b.BBY27,0) min_Price ,isnull(b.BBY19,0) BBY19,b2.BAG05,a3.ABC02,b.BDN01,b.BCH01,a.DSK01,b2.BAG23,a.BCK01A,0 ABC01,9 BCK18 ,a.VAF21 VBD12,0 BBY01A into #tmpVBD FROM #kbmVAFsd a join BBX1 a2 on a2.BBX01 = a.BBX01 JOIN BBY1 b ON a.BBY01 = b.BBY01 JOIN BAG1 b2 ON b.BBY01 = b2.BBY01 JOIN VAC1 a3 ON a.VAF06 = a3.VAC01 join BDA1 c on c.BDA01 = a.BDA01 JOIN BCK1 d ON d.BCK01 = a.BCK01B left JOIN AAS1 e ON b.AAS01 = e.AAS01 WHERE a.BDA01 >= ''1'' And a.BDA01 <= ''3'' And @aDate < b.BBY31 UNION ALL SELECT a2.BBX05 As NVAF22,c.BDA02,b2.BBY05 as VAF22,a.VAF21*b2.BDU04 AS VAF21 ,case when (b2.BDN01 <= ''3'') then dbo.GetDrugDept(1,b2.BBY01,a.BCK01A,1) else d.BCK03 end BCK03 ,e.AAS02,b2.BBY08,CASE b2.BDU06 WHEN 1 THEN ''是'' ELSE ''否'' END AS FBDU06,'''' FBDU10,b2.BDU06,b2.BDU05 ,b2.BBY25 lPrice,b2.BDU07,b2.BDU10 ,case when (b2.BDN01 <= ''3'') then cast(dbo.GetDrugDept(1,b2.BBY01,a.BCK01A,0) as int) else a.BCK01B end BCK01B ,a.VAF01,b2.BBY01,a.VAFID ,a.BDA01,b2.BBY25 A_Price,b2.BBY25 B_price,isnull(b2.BBY28,0),isnull(b2.BBY27,0),isnull(b2.BBY19,0) ,0 BAG05,a3.ABC02,b2.BDN01,b2.BCH01,a.DSK01,0 BAG23,a.BCK01A,0 ABC01,9,b2.BDU04 VBD12,b2.BBY01A FROM #kbmVAFsd a JOIN BBX1 a2 ON a.BBX01 = a2.BBX01 JOIN BDA1 c ON a.BDA01 = c.BDA01 JOIN #kbmbdu b2 ON a.BBX01 = b2.BBX01 and b2.BDU07=1 JOIN VAC1 a3 ON a.VAF06 = a3.VAC01 LEFT JOIN BCK1 d ON a.BCK01B = d.BCK01 LEFT JOIN AAS1 e ON b2.AAS01 = e.AAS01 WHERE a.VAF01A = 0 AND a.BDA01 = ''E'' UNION ALL SELECT a.VAF14+'' : ''+a.VAF15 As NVAF22,c.BDA02,b2.BBY05 as VAF22,a.VAF21*b2.BDU04 AS VAF21 ,case when (b2.BDN01 <= ''3'') then dbo.GetDrugDept(1,b2.BBY01,a.BCK01A,1) else d.BCK03 end BCK03 ,e.AAS02,b2.BBY08,CASE b2.BDU06 WHEN 1 THEN ''是'' ELSE ''否'' END AS FBDU06,'''' FBDU10,b2.BDU06,b2.BDU05 ,b2.BBY25 lPrice,b2.BDU07,b2.BDU10 ,case when (b2.BDN01 <= ''3'') then cast(dbo.GetDrugDept(1,b2.BBY01,a.BCK01A,0) as int) else a.BCK01B end BCK01B ,a.VAF01,b2.BBY01,a.VAFID ,a.BDA01,b2.BBY25,b2.BBY25,isnull(b2.BBY28,0),isnull(b2.BBY27,0),isnull(b2.BBY19,0) ,0 BAG05,a3.ABC02,b2.BDN01,b2.BCH01,a.DSK01,0 BAG23,a.BCK01A,0 ABC01,9,b2.BDU04 VBD12,b2.BBY01A FROM #kbmVAFsd a JOIN BDA1 c ON a.BDA01 = c.BDA01 JOIN #kbmbdu b2 ON a.BBX01 = b2.BBX01 and b2.BDU07=2 and a.VAF14=b2.BDU08 and a.VAF15 = b2.BDU09 JOIN VAC1 a3 ON a.VAF06 = a3.VAC01 LEFT JOIN BCK1 d ON a.BCK01B = d.BCK01 LEFT JOIN AAS1 e ON b2.AAS01 = e.AAS01 WHERE a.VAF01A > 0 AND a.BDA01 = ''E'' UNION ALL SELECT a2.BBX05 As NVAF22,c.BDA02,b2.BBY05 as VAF22,a.VAF21*b2.BDU04 AS VAF21 ,case when (b2.BDN01 <= ''3'') then dbo.GetDrugDept(1,b2.BBY01,a.BCK01A,1) else d.BCK03 end BCK03 ,e.AAS02,b2.BBY08,CASE b2.BDU06 WHEN 1 THEN ''是'' ELSE ''否'' END AS FBDU06,'''' FBDU10,b2.BDU06,b2.BDU05 ,b2.BBY25 lPrice,b2.BDU07,b2.BDU10 ,case when (b2.BDN01 <= ''3'') then cast(dbo.GetDrugDept(1,b2.BBY01,a.BCK01A,0) as int) else a.BCK01B end BCK01B ,a.VAF01,b2.BBY01,a.VAFID ,a.BDA01,b2.BBY25,b2.BBY25,isnull(b2.BBY28,0),isnull(b2.BBY27,0),isnull(b2.BBY19,0) ,0 BAG05,a3.ABC02,b2.BDN01,b2.BCH01,a.DSK01,0 BAG23,a.BCK01A,0 ABC01,9,b2.BDU04 VBD12,b2.BBY01A FROM #kbmVAFsd a JOIN BBX1 a2 ON a.BBX01 = a2.BBX01 JOIN BDA1 c ON a.BDA01 = c.BDA01 JOIN #kbmbdu b2 ON a.BBX01 = b2.BBX01 and b2.BDU07=1 JOIN VAC1 a3 ON a.VAF06 = a3.VAC01 LEFT JOIN BCK1 d ON a.BCK01B = d.BCK01 LEFT JOIN AAS1 e ON b2.AAS01 = e.AAS01 WHERE a.BDA01 >= ''8'' And a.BDA01 not in(''E'',''M'') and (a.VAF01A=0 or (a.VAF01A>0 and isnull(a.VAF15,'''')='''')) UNION ALL SELECT a2.BBX05 As NVAF22,c.BDA02,b2.BBY05 as VAF22,a.VAF21*b2.BDU04 AS VAF21 ,case when (b2.BDN01 <= ''3'') then dbo.GetDrugDept(1,b2.BBY01,a.BCK01A,1) else d.BCK03 end BCK03 ,e.AAS02,b2.BBY08,CASE b2.BDU06 WHEN 1 THEN ''是'' ELSE ''否'' END AS FBDU06,'''' FBDU10,b2.BDU06,b2.BDU05 ,b2.BBY25 lPrice,b2.BDU07,b2.BDU10 ,case when (b2.BDN01 <= ''3'') then cast(dbo.GetDrugDept(1,b2.BBY01,a.BCK01A,0) as int) else a.BCK01B end BCK01B ,a.VAF01,b2.BBY01,a.VAFID ,a.BDA01,b2.BBY25,b2.BBY25,isnull(b2.BBY28,0),isnull(b2.BBY27,0),isnull(b2.BBY19,0) ,0 BAG05,a3.ABC02,b2.BDN01,b2.BCH01,a.DSK01,0 BAG23,a.BCK01A,0 ABC01,9,b2.BDU04 VBD12,b2.BBY01A FROM #kbmVAFsd a JOIN BBX1 a2 ON a.BBX01 = a2.BBX01 JOIN BDA1 c ON a.BDA01 = c.BDA01 JOIN #kbmbdu b2 ON a.BBX01 = b2.BBX01 and b2.BDU07=2 and b2.BDU08=a.VAF15 JOIN VAC1 a3 ON a.VAF06 = a3.VAC01 LEFT JOIN BCK1 d ON a.BCK01B = d.BCK01 LEFT JOIN AAS1 e ON b2.AAS01 = e.AAS01 WHERE a.VAF01A>0 and a.BDA01 >= ''8'' And a.BDA01 not in(''E'',''M'') and isnull(a.VAF15,'''')>'''' union all select a2.BBX05 NVAF22, c.BDA02,b.BBY05 VAF22,a.VAF21,d.BCK03,e.AAS02,b.BBY08,''否'' AS FBDU06 , '''' AS FBDU10,0 as BDU06,1 as BDU05,b.BBY25 lPrice,1 BDU07,0 BDU10,a.BCK01B BCK01,a.VAF01,b.BBY01,a.VAFID ,a.BDA01,b.BBY25 A_Price,b.BBY25 B_price,isnull(b.BBY28,0) max_Price,isnull(b.BBY27,0) min_Price ,isnull(b.BBY19,0) BBY19,0 BAG05,a3.ABC02,b.BDN01,b.BCH01,a.DSK01, 0 BAG23,a.BCK01A,0 ABC01,9 BCK18,a.VAF21 VBD12, 0 BBY01A FROM #kbmVAFsd a join BBX1 a2 on a2.BBX01 = a.BBX01 JOIN BBY1 b ON a.BBY01 = b.BBY01 JOIN VAC1 a3 ON a.VAF06 = a3.VAC01 join BDA1 c on c.BDA01 = a.BDA01 JOIN BCK1 d ON d.BCK01 = a.BCK01B left JOIN AAS1 e ON b.AAS01 = e.AAS01 WHERE a.BDA01 in(''4'',''M'') And @aDate < b.BBY31 select a.BCK01,a.BBY01,min(a.DSK_ID) DSK01 into #kbmDpt from DPT1 a join (select BCK01,BBY01 from #tmpVBD where BDN01 >=''1'' and BDN01 <= ''3'') b on b.BCK01 = a.BCK01 and b.BBY01 = a.BBY01 where a.Quantity > 0.000001 and a.RefQty > 0.000001 group by a.BCK01,a.BBY01 UPDATE a set a.ABC01 = 1 from #tmpVBD a join ABC1 b on b.ABC02 = a.ABC02 where b.ACF01 in (1,3) and DATEDIFF(DAY,b.ABC12,@aDate)>=0 and DATEDIFF(DAY,@aDate,b.ABC13)>= 0 and (isnull(b.ABC05,0)=0 or (isnull(b.ABC05,0)=1 and exists(select * from ACW1 c where c.ABC02 = b.ABC02 and a.BCK01A = c.BCK01))) update a set a.BCK18 = case when ISNULL(f1.BBY01,0)<> 0 then f1.ACV10 when ISNULL(f1.BBY01,0)= 0 and ISNULL(g1.BCH01,'''') <> '''' then g1.ACV10 else a.BCK18 end FROM #tmpVBD a JOIN BBY1 c ON a.BBY01 = c.BBY01 LEFT JOIN (SELECT f.ABC02,f.BBY01,f.ACV10 FROM ACV1 f WHERE f.BCH01 IS NULL and ISNULL(f.ACV10,9)<>9) AS f1 ON a.ABC02=f1.ABC02 AND a.BBY01=f1.BBY01 LEFT JOIN (SELECT g.ABC02,g.BCH01,g.ACV10 FROM ACV1 g WHERE g.BBY01 IS NULL and ISNULL(g.ACV10,9)<>9) AS g1 ON a.ABC02=g1.ABC02 AND c.BCH01=g1.BCH01 AND NOT EXISTS(SELECT * FROM ACV1 g2 WHERE g2.BCH01 IS NULL AND a.ABC02=g2.ABC02 AND a.BBY01=g2.BBY01) where a.ABC01 = 1 and a.BDN01 >= ''1'' and a.BDN01 <= ''3'' update a set a.BCK18 = isnull(c.BCK18,0) from #tmpVBD a join BCK1 c on c.BCK01 = a.BCK01 where a.BDN01 >=''1'' and a.BDN01 <= ''3'' and a.BCK18 = 9 update a set a.lPrice = case when a.BCK18 = 1 and isnull(b.BAG23,0) > 0.000001 then b.BAG23 else a.lPrice end ,a.B_price = case when a.BCK18 = 1 and isnull(b.BAG23,0) > 0.000001 then b.BAG23 else a.B_price end ,a.A_Price = case when a.BCK18 = 1 and isnull(b.BAG23,0) > 0.000001 then b.BAG23 else a.A_Price end ,a.BAG23 = b.BAG23,a.DSK01 = d.DSK01,a.BBY08 = b.BDG02A from #tmpVBD a join BAG1 b on b.BBY01 = a.BBY01 left join #kbmDpt d on d.BCK01 = a.BCK01 and d.BBY01 = a.BBY01 where a.BDN01 >=''1'' and a.BDN01 <= ''3'' update a set a.lPrice = case when a.BCK18 = 1 then c.PurchasePrice else c.PresellPrice end ,a.B_price = case when a.BCK18 = 1 then c.PurchasePrice else c.PresellPrice end ,a.A_Price = case when a.BCK18 = 1 then c.PurchasePrice else c.PresellPrice end ,a.DSK01 = b.DSK01,a.BAG23 = c.PurchasePrice from #tmpVBD a join #kbmDpt b on b.BCK01 = a.BCK01 and b.BBY01 = a.BBY01 join DPT1 c on c.BCK01 = b.BCK01 and c.BBY01 = b.BBY01 and c.DSK_ID = b.DSK01 where a.BDN01 >=''1'' and a.BDN01 <= ''3'' and (a.BBY19 = 1 or (a.BBY19 = 0 and a.BCK18 = 1)) INSERT INTO #kbmVBDp(NVAF22,BDA02,VAF22,VAF21,BCK03,AAS02,BBY08,FBDU06, FBDU10,BDU06,BDU05,lPrice,BDU07,BDU10,BCK01,VAF01,BBY01,VAFID,BDA01 ,A_Price,B_Price,max_Price,min_Price,BBY19,DSK01,lMoney,BAG23,VBD12,BBY01A) SELECT a.NVAF22,a.BDA02,a.VAF22,a.VAF21,a.BCK03,a.AAS02,a.BBY08,a.FBDU06 ,a.FBDU10,a.BDU06,a.BDU05 ,CASE WHEN f1.BBY01 IS NOT NULL THEN (a.lPrice*a.BAG05*f1.ACV08)/f1.ACV09 WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.lPrice*a.BAG05*g1.ACV08)/g1.ACV09 ELSE a.lPrice*a.BAG05 END AS lPrice ,a.BDU07,a.BDU10,a.BCK01,a.VAF01,a.BBY01,a.VAFID,a.BDA01 ,a.A_Price,a.B_price,a.max_Price,a.min_Price,a.BBY19,a.DSK01 ,CASE WHEN f1.BBY01 IS NOT NULL THEN (a.lPrice*a.BAG05*f1.ACV08*a.VAF21)/f1.ACV09 WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.lPrice*a.BAG05*g1.ACV08*a.VAF21)/g1.ACV09 ELSE a.lPrice*a.BAG05*a.VAF21 END AS lMoney,a.BAG23,a.VBD12,a.BBY01A FROM #tmpVBD a LEFT JOIN (SELECT f.ABC02,f.BBY01,f.ACV06,f.ACV07,f.ACV08,f.ACV09,f.ACV10 FROM ACV1 f WHERE f.BCH01 IS NULL) AS f1 ON a.ABC02=f1.ABC02 AND a.BBY01=f1.BBY01 and a.ABC01 = 1 AND f1.ACV06<= (a.lPrice*a.BAG05*a.VAF21) AND f1.ACV07>=(a.lPrice*a.BAG05*a.VAF21) LEFT JOIN (SELECT g.ABC02,g.BCH01,g.ACV06,g.ACV07,g.ACV08,g.ACV09,g.ACV10 FROM ACV1 g WHERE g.BBY01 IS NULL) AS g1 ON a.ABC02=g1.ABC02 AND a.BCH01=g1.BCH01 and a.ABC01 = 1 AND NOT EXISTS(SELECT g2.BBY01 FROM ACV1 g2 WHERE g2.BCH01 IS NULL AND a.ABC02=g2.ABC02 AND a.BBY01=g2.BBY01) AND g1.ACV06<= (a.lPrice*a.BAG05*a.VAF21) AND g1.ACV07>=(a.lPrice*a.BAG05*a.VAF21) WHERE a.BDA01 >= ''1'' And a.BDA01 <= ''3'' union all SELECT a.NVAF22,a.BDA02,a.VAF22,a.VAF21,a.BCK03,a.AAS02,a.BBY08,a.FBDU06 ,a.FBDU10,a.BDU06,a.BDU05 ,CASE WHEN f1.BBY01 IS NOT NULL THEN (a.lPrice*f1.ACV08)/f1.ACV09 WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.lPrice*g1.ACV08)/g1.ACV09 ELSE a.lPrice END AS lPrice ,a.BDU07,a.BDU10,a.BCK01,a.VAF01,a.BBY01,a.VAFID,a.BDA01 ,a.A_Price,a.B_price,a.max_Price,a.min_Price,a.BBY19,a.DSK01 ,CASE WHEN f1.BBY01 IS NOT NULL THEN (a.lPrice*f1.ACV08*a.VAF21)/f1.ACV09 WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.lPrice*g1.ACV08*a.VAF21)/g1.ACV09 ELSE a.lPrice*a.VAF21 END AS lMoney,a.BAG23,a.VBD12,a.BBY01A FROM #tmpVBD a LEFT JOIN (SELECT f.ABC02,f.BBY01,f.ACV06,f.ACV07,f.ACV08,f.ACV09,f.ACV10 FROM ACV1 f WHERE f.BCH01 IS NULL) AS f1 ON a.ABC02=f1.ABC02 AND a.BBY01=f1.BBY01 and a.ABC01 = 1 AND f1.ACV06<= (a.lPrice*a.VAF21) AND f1.ACV07>=(a.lPrice*a.VAF21) LEFT JOIN (SELECT g.ABC02,g.BCH01,g.ACV06,g.ACV07,g.ACV08,g.ACV09,g.ACV10 FROM ACV1 g WHERE g.BBY01 IS NULL) AS g1 ON a.ABC02=g1.ABC02 AND a.BCH01=g1.BCH01 and a.ABC01 = 1 AND NOT EXISTS(SELECT g2.BBY01 FROM ACV1 g2 WHERE g2.BCH01 IS NULL AND a.ABC02=g2.ABC02 AND a.BBY01=g2.BBY01) AND g1.ACV06<= (a.lPrice*a.VAF21) AND g1.ACV07>=(a.lPrice*a.VAF21) WHERE a.BDA01 >= ''4'' DECLARE @lSum numeric(18,4) SET @lSum = (SELECT sum(lMoney) FROM #kbmVBDp) UPDATE #kbmVBDp SET lSum = @lSum Update a Set a.lVAFIDSum = b.lVSum From #kbmVBDp a join (Select VAFID,Sum(lMoney)as lVSum From #kbmVBDp Group by VAFID) b on a.VAFID = b.VAFID SELECT * FROM #kbmVBDp ORDER BY VAFID,VAF01 IF Object_id(''tempdb..#kbmVBDp'') IS NOT NULL DROP TABLE #kbmVBDp IF Object_id(''tempdb..#kbmVAFsd'') IS NOT NULL DROP TABLE #kbmVAFsd IF Object_id(''tempdb..#tmpVBD'') IS NOT NULL DROP TABLE #tmpVBD IF Object_id(''tempdb..#kbmDpt'') IS NOT NULL DROP TABLE #kbmDpt IF Object_id(''tempdb..#kbmbdu'') IS NOT NULL DROP TABLE #kbmbdu', '2016-09-19 18:24:10', '(8058)高瑜', 0) else print 'SYS_Scripts.id=7485 已经存在.' GO