sql实现把某一列的值当成列名来实现(待整理完善) |
您所在的位置:网站首页 › sql查询结果作为列名 › sql实现把某一列的值当成列名来实现(待整理完善) |
Create PROCEDURE [dbo].[Pro_Number_FactoryA] ( @DATADate nvarchar(10) ) AS BEGIN --存储当月天数日期的表 if object_id('tempdb..#tempTableDate') is not null Begin drop table #tempTableDate End create table #tempTableDate --创建临时表 ( StrsDate varchar(12) ) --判断是指定日期还是不区分日期 if(@DATADate='0000') begin --获得当前月份的工厂 SELECT distinct FactoryCode FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where DataTime like '%'+@DATADate+'%' end else begin --==============================================创建存储数据表 Start============================================================= --创建当月数据表 if object_id('tempdb..#tempTableA') is not null Begin drop table #tempTableA End --创建当月数据表 if object_id('tempdb..#tempTableB') is not null Begin drop table #tempTableB End --创建当月数据表 if object_id('tempdb..#tempTableC') is not null Begin drop table #tempTableC End create table #tempTableA --创建临时表 ( FactoryCode varchar(8), FactoryName varchar(80), FactoryType varchar(3), Parent_Factory varchar(8), TypeCode varchar(80) ) --通过工厂获得指定月份数据 Declare @DataTimeA varchar(10) --循环日期 DECLARE cursor_nameDate CURSOR FOR --定义游标 --SELECT distinct DataTime FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where DataTime like '%2022-10%' SELECT distinct DataTime FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where DataTime like '%'+@DATADate+'%' OPEN cursor_nameDate --打开游标 FETCH NEXT FROM cursor_nameDate INTO @DataTimeA--抓取下一行游标数据 WHILE @@FETCH_STATUS = 0 BEGIN --给临时表增加日期字段 DECLARE @ColumnName NVARCHAR(100) SET @ColumnName ='[' +CONVERT(NVARCHAR(100),@DataTimeA) + ']' --把日期存储到日期表中,后续会用到 insert into #tempTableDate(StrsDate) values(@ColumnName) EXECUTE('ALTER TABLE #tempTableA ADD ' + @ColumnName + ' NVARCHAR(100)') FETCH NEXT FROM cursor_nameDate INTO @DataTimeA END CLOSE cursor_nameDate --关闭游标 DEALLOCATE cursor_nameDate --释放游标 --==============================================创建存储数据表 End============================================================= --存储上一级数据结构表创建 select * into #tempTableB from #tempTableA select * into #tempTableC from #tempTableA --==============================================数据存储操作 Start============================================================= --获得当前月份的工厂 --insert into #tempTableA --SELECT distinct FactoryCode FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where DataTime like '%'+@DATADate+'%' --通过工厂获得指定月份数据 Declare @FactoryCodeA varchar(8) --获得当前月份的工厂,循环单位 DECLARE cursor_name CURSOR FOR --定义游标 --SELECT distinct FactoryCode FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where DataTime like '%2022-10%' SELECT distinct FactoryCode FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where DataTime like '%'+@DATADate+'%' OPEN cursor_name --打开游标 FETCH NEXT FROM cursor_name INTO @FactoryCodeA--抓取下一行游标数据 WHILE @@FETCH_STATUS = 0 BEGIN Declare @FactoryCode varchar(8) Declare @FactoryName varchar(80) Declare @FactoryType varchar(3) Declare @Parent_Factory varchar(8) Declare @Numbers int Declare @ManageNum int Declare @TalnetNum int Declare @TraineeNum int Declare @EpibolyNum int Declare @ShortNum int Declare @UnPositionNum int Declare @NimbleNum int Declare @TemporarilyNum int Declare @CreateTime datetime Declare @DataTime date Declare @ChanJia int Declare @ChanQianJia int Declare @ChangXiu int Declare @DuanXiu int Declare @GongShang int Declare @DaiGang_ChanJianEnd int Declare @Other int Declare @Reserve varchar(2) Declare @Reserve2 varchar(2) Declare @Reserve3 varchar(2) Declare @Reserve4 varchar(2) Declare @Reserve5 varchar(2) Declare @Reserve6 varchar(2) --把每个列以数值形式增加到表中 insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'Numbers' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'ManageNum' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'TalnetNum' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'EpibolyNum' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'ShortNum' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'UnPositionNum' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'NimbleNum' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'TemporarilyNum' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'ChanJia' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'ChanQianJia' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'ChangXiu' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'DuanXiu' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'GongShang' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'DaiGang_ChanJianEnd' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'Other' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'Reserve' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'Reserve2' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'Reserve3' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'Reserve4' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'Reserve5' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' insert into #tempTableA(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 FactoryCode,FactoryName,FactoryType,Parent_Factory,'Reserve6' FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' --循环单位的当月数据进行处理 DECLARE cursor_nameA CURSOR FOR --定义游标 --SELECT * FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode='50089129' and DataTime like '%2022-10%' order by DataTime SELECT FactoryCode,FactoryName,FactoryType,Parent_Factory,Numbers,ManageNum,TalnetNum,TraineeNum,EpibolyNum,ShortNum,UnPositionNum,NimbleNum,TemporarilyNum,CreateTime,DataTime,ChanJia,ChanQianJia,ChangXiu,DuanXiu,GongShang,DaiGang_ChanJianEnd,Other,Reserve,Reserve2,Reserve3,Reserve4,Reserve5,Reserve6 FROM [KhaosReport].[dbo].[T_Khaos_Number_Factory] where FactoryCode=@FactoryCodeA and DataTime like '%'+@DATADate+'%' order by DataTime OPEN cursor_nameA --打开游标 FETCH NEXT FROM cursor_nameA INTO @FactoryCode,@FactoryName,@FactoryType,@Parent_Factory,@Numbers,@ManageNum,@TalnetNum,@TraineeNum,@EpibolyNum,@ShortNum,@UnPositionNum,@NimbleNum,@TemporarilyNum,@CreateTime,@DataTime,@ChanJia,@ChanQianJia,@ChangXiu,@DuanXiu,@GongShang,@DaiGang_ChanJianEnd,@Other,@Reserve,@Reserve2,@Reserve3,@Reserve4,@Reserve5,@Reserve6--抓取下一行游标数据 WHILE @@FETCH_STATUS = 0 BEGIN --拼接日期字段名称 DECLARE @ColumnDataTime NVARCHAR(100) SET @ColumnDataTime ='[' +CONVERT(NVARCHAR(100),@DataTime) + ']' --给日期字段赋值 set @Numbers=ISNULL(@Numbers,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @Numbers +' where TypeCode=''Numbers'' and FactoryCode='+@FactoryCode ) set @ManageNum=ISNULL(@ManageNum,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @ManageNum +' where TypeCode=''ManageNum'' and FactoryCode='+@FactoryCode ) set @TalnetNum=ISNULL(@TalnetNum,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @TalnetNum +' where TypeCode=''TalnetNum'' and FactoryCode='+@FactoryCode ) set @TraineeNum=ISNULL(@TraineeNum,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @TraineeNum +' where TypeCode=''TraineeNum'' and FactoryCode='+@FactoryCode ) set @EpibolyNum=ISNULL(@EpibolyNum,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @EpibolyNum +' where TypeCode=''EpibolyNum'' and FactoryCode='+@FactoryCode ) set @ShortNum=ISNULL(@ShortNum,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @ShortNum +' where TypeCode=''ShortNum'' and FactoryCode='+@FactoryCode ) set @UnPositionNum=ISNULL(@UnPositionNum,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @UnPositionNum +' where TypeCode=''UnPositionNum'' and FactoryCode='+@FactoryCode ) set @NimbleNum=ISNULL(@NimbleNum,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @NimbleNum +' where TypeCode=''NimbleNum'' and FactoryCode='+@FactoryCode ) set @TemporarilyNum=ISNULL(@TemporarilyNum,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @TemporarilyNum +' where TypeCode=''TemporarilyNum'' and FactoryCode='+@FactoryCode ) set @ChanJia=ISNULL(@ChanJia,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @ChanJia +' where TypeCode=''ChanJia'' and FactoryCode='+@FactoryCode ) set @ChanQianJia=ISNULL(@ChanQianJia,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @ChanQianJia +' where TypeCode=''ChanQianJia'' and FactoryCode='+@FactoryCode ) set @ChangXiu=ISNULL(@ChangXiu,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @ChangXiu +' where TypeCode=''ChangXiu'' and FactoryCode='+@FactoryCode ) set @DuanXiu=ISNULL(@DuanXiu,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @DuanXiu +' where TypeCode=''DuanXiu'' and FactoryCode='+@FactoryCode ) set @GongShang=ISNULL(@GongShang,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @GongShang +' where TypeCode=''GongShang'' and FactoryCode='+@FactoryCode ) set @DaiGang_ChanJianEnd=ISNULL(@DaiGang_ChanJianEnd,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @DaiGang_ChanJianEnd +' where TypeCode=''DaiGang_ChanJianEnd'' and FactoryCode='+@FactoryCode ) set @Other=ISNULL(@Other,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @Other +' where TypeCode=''Other'' and FactoryCode='+@FactoryCode ) set @Reserve=ISNULL(@Reserve,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @Reserve +' where TypeCode=''Reserve'' and FactoryCode='+@FactoryCode ) set @Reserve2=ISNULL(@Reserve2,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @Reserve2 +' where TypeCode=''Reserve2'' and FactoryCode='+@FactoryCode ) set @Reserve3=ISNULL(@Reserve3,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @Reserve3 +' where TypeCode=''Reserve3'' and FactoryCode='+@FactoryCode ) set @Reserve4=ISNULL(@Reserve4,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @Reserve4 +' where TypeCode=''Reserve4'' and FactoryCode='+@FactoryCode ) set @Reserve5=ISNULL(@Reserve5,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @Reserve5 +' where TypeCode=''Reserve5'' and FactoryCode='+@FactoryCode ) set @Reserve6=ISNULL(@Reserve6,0) EXECUTE('update #tempTableA set ' + @ColumnDataTime + '='+ @Reserve6 +' where TypeCode=''Reserve6'' and FactoryCode='+@FactoryCode ) FETCH NEXT FROM cursor_nameA INTO @FactoryCode,@FactoryName,@FactoryType,@Parent_Factory,@Numbers,@ManageNum,@TalnetNum,@TraineeNum,@EpibolyNum,@ShortNum,@UnPositionNum,@NimbleNum,@TemporarilyNum,@CreateTime,@DataTime,@ChanJia,@ChanQianJia,@ChangXiu,@DuanXiu,@GongShang,@DaiGang_ChanJianEnd,@Other,@Reserve,@Reserve2,@Reserve3,@Reserve4,@Reserve5,@Reserve6 END CLOSE cursor_nameA --关闭游标 DEALLOCATE cursor_nameA --释放游标 FETCH NEXT FROM cursor_name INTO @FactoryCodeA END CLOSE cursor_name --关闭游标 DEALLOCATE cursor_name --释放游标 --==============================================数据存储操作 End============================================================= --==============================================上一级数据存储操作 Start====================================================== --通过工厂获得指定月份数据 Declare @Parent_FactoryA varchar(8) --获得当前月份的工厂,循环单位 DECLARE cursor_nameParent CURSOR FOR --定义游标 select distinct Parent_Factory from #tempTableA OPEN cursor_nameParent --打开游标 FETCH NEXT FROM cursor_nameParent INTO @Parent_FactoryA--抓取下一行游标数据 WHILE @@FETCH_STATUS = 0 BEGIN --把每个列以数值形式增加到表中 insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'Numbers' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'ManageNum' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'TalnetNum' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'EpibolyNum' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'ShortNum' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'UnPositionNum' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'NimbleNum' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'TemporarilyNum' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'ChanJia' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'ChanQianJia' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'ChangXiu' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'DuanXiu' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'GongShang' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'DaiGang_ChanJianEnd' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'Other' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'Reserve' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'Reserve2' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'Reserve3' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'Reserve4' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'Reserve5' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA insert into #tempTableB(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'Reserve6' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryA --通过工厂获得指定月份数据 Declare @StrsDate varchar(12) --获得当前月份的工厂,循环单位 DECLARE cursor_nameParentA CURSOR FOR --定义游标 SELECT StrsDate FROM #tempTableDate OPEN cursor_nameParentA --打开游标 FETCH NEXT FROM cursor_nameParentA INTO @StrsDate--抓取下一行游标数据 WHILE @@FETCH_STATUS = 0 BEGIN --给日期字段赋值 --update #tempTableB set @StrsDate=(select sum(CAST( ISNULL(@StrsDate,0)as int)) from #tempTableA where Parent_Factory=@Parent_FactoryA and TypeCode='Numbers') where FactoryCode=@Parent_FactoryA and TypeCode='Numbers' EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''Numbers'') where TypeCode=''Numbers'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''ManageNum'') where TypeCode=''ManageNum'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''TalnetNum'') where TypeCode=''TalnetNum'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''EpibolyNum'') where TypeCode=''EpibolyNum'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''ShortNum'') where TypeCode=''ShortNum'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''UnPositionNum'') where TypeCode=''UnPositionNum'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''NimbleNum'') where TypeCode=''NimbleNum'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''TemporarilyNum'') where TypeCode=''TemporarilyNum'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''ChanJia'') where TypeCode=''ChanJia'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''ChanQianJia'') where TypeCode=''ChanQianJia'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''ChangXiu'') where TypeCode=''ChangXiu'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''DuanXiu'') where TypeCode=''DuanXiu'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''GongShang'') where TypeCode=''GongShang'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''DaiGang_ChanJianEnd'') where TypeCode=''DaiGang_ChanJianEnd'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''Other'') where TypeCode=''Other'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''Reserve'') where TypeCode=''Reserve'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''Reserve2'') where TypeCode=''Reserve2'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''Reserve3'') where TypeCode=''Reserve3'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''Reserve4'') where TypeCode=''Reserve4'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''Reserve5'') where TypeCode=''Reserve5'' and FactoryCode='+@Parent_FactoryA ) EXECUTE('update #tempTableB set ' + @StrsDate + '=(select sum(CAST( ISNULL('+ @StrsDate +',0)as int)) from #tempTableA where Parent_Factory='+@Parent_FactoryA+' and TypeCode=''Reserve6'') where TypeCode=''Reserve6'' and FactoryCode='+@Parent_FactoryA ) FETCH NEXT FROM cursor_nameParentA INTO @StrsDate END CLOSE cursor_nameParentA --关闭游标 DEALLOCATE cursor_nameParentA --释放游标 FETCH NEXT FROM cursor_nameParent INTO @Parent_FactoryA END CLOSE cursor_nameParent --关闭游标 DEALLOCATE cursor_nameParent --释放游标 --==============================================上一级数据存储操作 End======================================================= --==============================================顶级数据存储操作 Start====================================================== --通过工厂获得指定月份数据 Declare @Parent_FactoryTop varchar(8) --获得当前月份的工厂,循环单位 DECLARE cursor_nameParentTop CURSOR FOR --定义游标 select distinct Parent_Factory from #tempTableB OPEN cursor_nameParentTop --打开游标 FETCH NEXT FROM cursor_nameParentTop INTO @Parent_FactoryTop--抓取下一行游标数据 WHILE @@FETCH_STATUS = 0 BEGIN --把每个列以数值形式增加到表中 insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'Numbers' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'ManageNum' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'TalnetNum' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'EpibolyNum' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'ShortNum' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'UnPositionNum' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'NimbleNum' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'TemporarilyNum' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'ChanJia' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'ChanQianJia' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'ChangXiu' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'DuanXiu' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'GongShang' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'DaiGang_ChanJianEnd' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'Other' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'Reserve' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'Reserve2' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'Reserve3' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'Reserve4' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'Reserve5' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop insert into #tempTableC(FactoryCode,FactoryName,FactoryType,Parent_Factory,TypeCode) SELECT top 1 org_code,org_name,org_type,parent_code,'Reserve6' FROM [KhaosReport].[dbo].[t_khaos_fac_subordinate_relation] where org_code=@Parent_FactoryTop --通过工厂获得指定月份数据 Declare @StrsDateTop varchar(12) --获得当前月份的工厂,循环单位 DECLARE cursor_nameParentTopA CURSOR FOR --定义游标 SELECT StrsDate FROM #tempTableDate OPEN cursor_nameParentTopA --打开游标 FETCH NEXT FROM cursor_nameParentTopA INTO @StrsDateTop--抓取下一行游标数据 WHILE @@FETCH_STATUS = 0 BEGIN --给日期字段赋值 --update #tempTableB set @StrsDate=(select sum(CAST( ISNULL(@StrsDate,0)as int)) from #tempTableA where Parent_Factory=@Parent_FactoryA and TypeCode='Numbers') where FactoryCode=@Parent_FactoryA and TypeCode='Numbers' EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''Numbers'') where TypeCode=''Numbers'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''ManageNum'') where TypeCode=''ManageNum'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''TalnetNum'') where TypeCode=''TalnetNum'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''EpibolyNum'') where TypeCode=''EpibolyNum'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''ShortNum'') where TypeCode=''ShortNum'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''UnPositionNum'') where TypeCode=''UnPositionNum'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''NimbleNum'') where TypeCode=''NimbleNum'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''TemporarilyNum'') where TypeCode=''TemporarilyNum'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''ChanJia'') where TypeCode=''ChanJia'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''ChanQianJia'') where TypeCode=''ChanQianJia'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''ChangXiu'') where TypeCode=''ChangXiu'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''DuanXiu'') where TypeCode=''DuanXiu'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''GongShang'') where TypeCode=''GongShang'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''DaiGang_ChanJianEnd'') where TypeCode=''DaiGang_ChanJianEnd'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''Other'') where TypeCode=''Other'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''Reserve'') where TypeCode=''Reserve'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''Reserve2'') where TypeCode=''Reserve2'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''Reserve3'') where TypeCode=''Reserve3'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''Reserve4'') where TypeCode=''Reserve4'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''Reserve5'') where TypeCode=''Reserve5'' and FactoryCode='+@Parent_FactoryTop ) EXECUTE('update #tempTableC set ' + @StrsDateTop + '=(select sum(CAST( ISNULL('+ @StrsDateTop +',0)as int)) from #tempTableB where Parent_Factory='+@Parent_FactoryTop+' and TypeCode=''Reserve6'') where TypeCode=''Reserve6'' and FactoryCode='+@Parent_FactoryTop ) FETCH NEXT FROM cursor_nameParentTopA INTO @StrsDateTop END CLOSE cursor_nameParentTopA --关闭游标 DEALLOCATE cursor_nameParentTopA --释放游标 FETCH NEXT FROM cursor_nameParentTop INTO @Parent_FactoryTop END CLOSE cursor_nameParentTop --关闭游标 DEALLOCATE cursor_nameParentTop --释放游标 --==============================================顶级数据存储操作 End======================================================= end --select * from #tempTableA --select * from #tempTableB --select * from #tempTableC --==============================================按级合并数据 Start======================================================= --通过工厂获得指定月份数据 Declare @Parent_FactorySummary varchar(8) --获得当前月份的工厂,循环单位 DECLARE cursor_nameParentSummary CURSOR FOR --定义游标 select distinct FactoryCode from #tempTableB order by FactoryCode OPEN cursor_nameParentSummary --打开游标 FETCH NEXT FROM cursor_nameParentSummary INTO @Parent_FactorySummary--抓取下一行游标数据 WHILE @@FETCH_STATUS = 0 BEGIN insert into #tempTableC select * from #tempTableB where FactoryCode=@Parent_FactorySummary insert into #tempTableC select * from #tempTableA where Parent_Factory=@Parent_FactorySummary order by FactoryCode FETCH NEXT FROM cursor_nameParentSummary INTO @Parent_FactorySummary END CLOSE cursor_nameParentSummary --关闭游标 DEALLOCATE cursor_nameParentSummary --释放游标 --==============================================按级合并数据 End======================================================= --获取最后数据 select * from #tempTableC order by FactoryType,FactoryCode --工厂的去重集合 select distinct FactoryCode,FactoryName,FactoryType,Parent_Factory from #tempTableC order by FactoryType,FactoryCode select * from [dbo].[T_Khaos_Number_Factory] where DataTime like '%'+@DATADate+'%' END GO
|
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |