mysql 创建临时表 自增字段_在存储过程创建临时表，插入字段，更新字段

1 CREATE PROCEDURE [dbo].[Proc_PX_Situation]2 @BMDateFrom nvarchar(50), --报名时间 从3 @BMDateEnd nvarchar(50), --报名时间 到4 @BenlonFenJu nvarchar(50) --所属分局5 AS6 begin7 --declare @SqlStr nvarchar(2000);8 create table #temp_table(9 Row_ID int,10 RowGuid varchar(50), --计划Guid11 --PX_Plan12 PlanName nvarchar(100), --培训名称13 UnitCount int, --计划参加企业总数14 UserCount int, --计划参加人员总数15 --PX_BaoM16 BMUnitCount int, --实际报名企业总数 某一个计划下面报名的 DanWeiName的数量,不重复17 BMUserCount int, --实际报名人员总数 某一个计划下面报名的 Count(UserGuid)18 PayUnitCount int, --实际交费企业总数19 PayUserCount int, --实际交费人员总数20 PXUnitCount int, --实际培训企业总数21 PXUserCountt int , --实际培训人员总数22 BaoMDNum nvarchar(50) --报名点23 -- BaoMdate datetime --报名时间24 )25

26 insert into #temp_table(27 Row_ID,28 RowGuid , --计划Guid29 --PX_Plan30 PlanName , --培训名称31 UnitCount , --计划参加企业总数32 UserCount , --计划参加人员总数33 --PX_BaoM34 BMUnitCount , --实际报名企业总数 某一个计划下面报名的 DanWeiName的数量,不重复35 BMUserCount , --实际报名人员总数 某一个计划下面报名的 Count(UserGuid)36 PayUnitCount , --实际交费企业总数37 PayUserCount , --实际交费人员总数38 PXUnitCount , --实际培训企业总数39 PXUserCountt , --实际培训人员总数40 BaoMDNum --报名点41 -- BaoMdate --报名时间42 )43 (select Row_ID, RowGuid,PlanName,'','','','','','','','','' fromPX_Plan)44

45 --统计start46

47 --计划参加企业总数48 if @BenlonFenJu=''

49 update a set a.UnitCount=b.UnitCount from#temp_table a,50 (51 select RowGuid,UnitCount frompx_plan52 )b where a.RowGuid=b.RowGuid53 else

54 update a set a.UnitCount=b.UnitCount from#temp_table a,55 (56 select PlanGuid,UnitCount,BaoMDCode from PX_PlanBaoMD where BaoMDCode=@BenlonFenJu57 )b where a.RowGuid=b.PlanGuid58

59 --计划参加人员总数60 if @BenlonFenJu=''

61 update a set a.UserCount=b.UserCount from#temp_table a,62 (63 select RowGuid,UserCount frompx_plan64 )b where a.RowGuid=b.RowGuid65 else

66 update a set a.UserCount=b.UserCount from#temp_table a,67 (68 select PlanGuid,UserCount,BaoMDCode from PX_PlanBaoMD where BaoMDCode=@BenlonFenJu69 )b where a.RowGuid=b.PlanGuid70

71 --实际报名企业总数72 update a set a.BMUnitCount=b.total from#temp_table a,73 (74 select planguid, count(distinct DanWeiGuid) total from(75 select BMAddDate,planguid,DanWeiGuid fromView_PX_Plan_BaoM76 where BMAddDate >(case @BMDateFrom when '' then '1900-01-01' else@BMDateFrom end)77 and BMAddDate

82 --实际报名人员总数83 update a set a.BMUserCount=b.total from#temp_table a,84 (85 select planguid,count(UserGuid) total from(86 select BMAddDate,planguid,UserGuid fromView_PX_Plan_BaoM87 where BMAddDate >(case @BMDateFrom when '' then '1900-01-01' else@BMDateFrom end)88 and BMAddDate

93 --实际交费企业总数94 update a set a.PayUnitCount=b.total from#temp_table a,95 (select planguid,count(distinct DanWeiGuid) total from(96 select BMAddDate,planguid,DanWeiGuid fromView_PX_Plan_BaoM97 where IsPay=1

98 and BMAddDate >(case @BMDateFrom when '' then '1900-01-01' else@BMDateFrom end)99 and BMAddDate

104 --实际交费人员总数105 update a set a.PayUserCount=b.total from#temp_table a,106 (select planguid,count(UserGuid) total from(107 select BMAddDate,planguid,UserGuid fromView_PX_Plan_BaoM108 where IsPay=1

109 and BMAddDate >(case @BMDateFrom when '' then '1900-01-01' else@BMDateFrom end)110 and BMAddDate

115 --实际培训企业总数116 update a set a.PXUnitCount=b.total from#temp_table a,117 (select planguid,count(distinct DanWeiGuid) total from(118 select BMAddDate,planguid,DanWeiGuid fromView_PX_Plan_BaoM119 where IsPay=1 and IsPeiXun=1

120 and BMAddDate >(case @BMDateFrom when '' then '1900-01-01' else@BMDateFrom end)121 and BMAddDate

126 --实际培训人员总数127 update a set a.PXUserCountt=b.total from#temp_table a,128 (select planguid,count(UserGuid) total from(129 select BMAddDate,planguid,UserGuid fromView_PX_Plan_BaoM130 where IsPay=1 and IsPeiXun=1

131 and BMAddDate >(case @BMDateFrom when '' then '1900-01-01' else@BMDateFrom end)132 and BMAddDate

137 --报名点138 update a set a.BaoMDNum=b.BaoMDNum from#temp_table a,139 (select distinct planguid,BaoMDNum from View_PX_Plan_BaoM)b where a.RowGuid=b.PlanGuid140

141

142 --报名时间 --AddDate143 --update a set a.BaoMdate=b.BMAddDate from#temp_table a,144 --(select planguid,DanWeiGuid, BMAddDate from View_PX_Plan_BaoM)b where a.RowGuid=b.PlanGuid145 --统计end146

147 select * from#temp_table order by Row_ID DESC148

149 end150

151

152

153

154

155

156 GO