数据库 动态表

USE [Platform_SM_v3.0]
GO

/****** Object:  StoredProcedure [dbo].[SYNC_GL_RY_His_Count]    Script Date: 2017/4/2 18:08:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE proc [dbo].[SYNC_GL_RY_His_Count]
@mineName varchar(20)--矿井名称    
as 
begin
declare @num1 int -- -- 临时变量
declare @num2 int -- -- 临时变量
declare @num3 int -- -- 临时变量
declare @num4 int -- -- 临时变量
declare @num5 int -- -- 临时变量
declare @num6 int -- -- 临时变量
declare @num7 int -- -- 临时变量
declare @num8 int -- -- 临时变量
declare @num9 int -- -- 临时变量
declare @num10 int -- -- 临时变量
declare @num11 int -- -- 临时变量
declare @num12 int -- -- 临时变量
set @num1=0 
set @num2=0 
set @num3=0 
set @num4=0 
set @num5=0 
set @num6=0 
set @num7=0 
set @num8=0 
set @num9=0 
set @num10=0 
set @num11=0 
set @num12=0 
declare @sqls nvarchar(400) -- -- 临时变量 
declare @sqls1 nvarchar(400) -- -- 临时变量 
declare @sqls2 nvarchar(400) -- -- 临时变量 
declare @sqls3 nvarchar(400) -- -- 临时变量 
declare @sqls4 nvarchar(400) -- -- 临时变量 
declare @sqls5 nvarchar(400) -- -- 临时变量 
declare @sqls6 nvarchar(400) -- -- 临时变量 
declare @sqls7 nvarchar(400) -- -- 临时变量 
declare @sqls8 nvarchar(400) -- -- 临时变量 
declare @sqls9 nvarchar(400) -- -- 临时变量 
declare @sqls10 nvarchar(400) -- -- 临时变量 
declare @sqls11 nvarchar(400) -- -- 临时变量 
declare @year varchar(100)
set @year=Datename(YEAR,GetDate())
 
set @sqls='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'01 where MineName='''+@mineName+''''
set @sqls1='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'02 where MineName='''+@mineName+''''
set @sqls2='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'03 where MineName='''+@mineName+''''
set @sqls3='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'04 where MineName='''+@mineName+''''
set @sqls4='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'05 where MineName='''+@mineName+''''
set @sqls5='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'06 where MineName='''+@mineName+''''
set @sqls6='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'07 where MineName='''+@mineName+''''
set @sqls7='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'08 where MineName='''+@mineName+''''
set @sqls8='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'09 where MineName='''+@mineName+''''
set @sqls9='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'10 where MineName='''+@mineName+''''
set @sqls10='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'11 where MineName='''+@mineName+''''
set @sqls11='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'12 where MineName='''+@mineName+''''
if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'01')>0 --判断表是否存在
exec sp_executesql @sqls,N'@a int output',@num1 output 
if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'02')>0
exec sp_executesql @sqls1,N'@a int output',@num2 output 
if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'03')>0
exec sp_executesql @sqls2,N'@a int output',@num3 output 
if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'04')>0
exec sp_executesql @sqls3,N'@a int output',@num4 output 
if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'05')>0
exec sp_executesql @sqls4,N'@a int output',@num5 output 
if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'06')>0
exec sp_executesql @sqls5,N'@a int output',@num6 output 
if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'07')>0
exec sp_executesql @sqls6,N'@a int output',@num7 output 
if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'08')>0
exec sp_executesql @sqls7,N'@a int output',@num8 output 
if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'09')>0
exec sp_executesql @sqls8,N'@a int output',@num9 output 
if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'10')>0
exec sp_executesql @sqls9,N'@a int output',@num10 output 
if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'11')>0
exec sp_executesql @sqls10,N'@a int output',@num11 output 
if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'12')>0
exec sp_executesql @sqls11,N'@a int output',@num12 output 

select '1'as month, @num1 as num
Union All
select '2'as month, @num2 as num
Union All
select '3'as month, @num3 as num
Union All
select '4'as month, @num4 as num
Union All
select '5'as month, @num5 as num
Union All
select '6'as month, @num6 as num
Union All
select '7'as month, @num7 as num
Union All
select '8'as month, @num8 as num
Union All
select '9'as month, @num9 as num
Union All
select '10'as month, @num10 as num
Union All
select '11'as month, @num11 as num
Union All
select '12'as month, @num12 as num

end


GO
--注释：Union All 可以将上下两张表结果统计成一张表，前提是表结构必须相同

 

USE [Platform_SM_v3.0]GO
/****** Object:  StoredProcedure [dbo].[SYNC_GL_RY_His_Count]    Script Date: 2017/4/2 18:08:51 ******/SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO

CREATE proc [dbo].[SYNC_GL_RY_His_Count]@mineName varchar(20)--矿井名称as begindeclare @num1 int -- -- 临时变量declare @num2 int -- -- 临时变量declare @num3 int -- -- 临时变量declare @num4 int -- -- 临时变量declare @num5 int -- -- 临时变量declare @num6 int -- -- 临时变量declare @num7 int -- -- 临时变量declare @num8 int -- -- 临时变量declare @num9 int -- -- 临时变量declare @num10 int -- -- 临时变量declare @num11 int -- -- 临时变量declare @num12 int -- -- 临时变量set @num1=0 set @num2=0 set @num3=0 set @num4=0 set @num5=0 set @num6=0 set @num7=0 set @num8=0 set @num9=0 set @num10=0 set @num11=0 set @num12=0 declare @sqls nvarchar(400) -- -- 临时变量 declare @sqls1 nvarchar(400) -- -- 临时变量 declare @sqls2 nvarchar(400) -- -- 临时变量 declare @sqls3 nvarchar(400) -- -- 临时变量 declare @sqls4 nvarchar(400) -- -- 临时变量 declare @sqls5 nvarchar(400) -- -- 临时变量 declare @sqls6 nvarchar(400) -- -- 临时变量 declare @sqls7 nvarchar(400) -- -- 临时变量 declare @sqls8 nvarchar(400) -- -- 临时变量 declare @sqls9 nvarchar(400) -- -- 临时变量 declare @sqls10 nvarchar(400) -- -- 临时变量 declare @sqls11 nvarchar(400) -- -- 临时变量 declare @year varchar(100)set @year=Datename(YEAR,GetDate()) set @sqls='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'01 where MineName='''+@mineName+''''set @sqls1='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'02 where MineName='''+@mineName+''''set @sqls2='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'03 where MineName='''+@mineName+''''set @sqls3='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'04 where MineName='''+@mineName+''''set @sqls4='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'05 where MineName='''+@mineName+''''set @sqls5='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'06 where MineName='''+@mineName+''''set @sqls6='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'07 where MineName='''+@mineName+''''set @sqls7='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'08 where MineName='''+@mineName+''''set @sqls8='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'09 where MineName='''+@mineName+''''set @sqls9='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'10 where MineName='''+@mineName+''''set @sqls10='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'11 where MineName='''+@mineName+''''set @sqls11='select @a=count(*) from GL_RyMonthCheckRecord'+@year+'12 where MineName='''+@mineName+''''if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'01')>0exec sp_executesql @sqls,N'@a int output',@num1 output if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'02')>0exec sp_executesql @sqls1,N'@a int output',@num2 output if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'03')>0exec sp_executesql @sqls2,N'@a int output',@num3 output if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'04')>0exec sp_executesql @sqls3,N'@a int output',@num4 output if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'05')>0exec sp_executesql @sqls4,N'@a int output',@num5 output if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'06')>0exec sp_executesql @sqls5,N'@a int output',@num6 output if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'07')>0exec sp_executesql @sqls6,N'@a int output',@num7 output if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'08')>0exec sp_executesql @sqls7,N'@a int output',@num8 output if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'09')>0exec sp_executesql @sqls8,N'@a int output',@num9 output if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'10')>0exec sp_executesql @sqls9,N'@a int output',@num10 output if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'11')>0exec sp_executesql @sqls10,N'@a int output',@num11 output if (select count(*) from sysobjects where name='GL_RyMonthCheckRecord'+@year+'12')>0exec sp_executesql @sqls11,N'@a int output',@num12 output 
select '1'as month, @num1 as numUnion Allselect '2'as month, @num2 as numUnion Allselect '3'as month, @num3 as numUnion Allselect '4'as month, @num4 as numUnion Allselect '5'as month, @num5 as numUnion Allselect '6'as month, @num6 as numUnion Allselect '7'as month, @num7 as numUnion Allselect '8'as month, @num8 as numUnion Allselect '9'as month, @num9 as numUnion Allselect '10'as month, @num10 as numUnion Allselect '11'as month, @num11 as numUnion Allselect '12'as month, @num12 as num
end

GO