22,207
社区成员
发帖
与我相关
我的任务
分享
--能不能建分区视图
create view all_data
as
.......
select * from table_2005
union all
select * from table_2007
........
go
select * from all_data where 时间 between 时间1 and 时间2
alter procedure proc1(@var1 datetime, @var2 datetime)
--@var2是最近时间
as
declare @sqltmp varchar(1000)
declare @len int --时间差(年)
declare @bztime datetime--标志存储当前的数据库截断时间
set @bztime = ( select sj from backlsyygs.dbo.xszdsj);
--@len存储时间差
set @len = cast(year(@var2) as int)- cast(year(@var1) as int);
if (@len=0 and @var1 > @bztime)--大于标志位,不垮年,不跨当前数据库,从当前数据库取
set @sqltmp = 'select * from lsyygs.dbo.xsdetail where sj>= '+''''+convert(varchar(10),@var1,126)+''''+' and sj<=' + ''''+convert(varchar(10),@var2,126)+'''';
if (@len=0 and @var2<= @bztime)--小于标志位,不跨年,不涉及当前数据库,从以前年份备份数据库取数
begin
set @sqltmp = 'select * from backlsyygs.dbo.xsdetail'+''+cast(year(@var1) as varchar(4)) +''+' where sj>= '+''''+convert(varchar(10),@var1,126)+''''+' and sj<=' + '''';
set @sqltmp = @sqltmp+convert(varchar(10),@var2,126)+'''';
end
if(@len =0 and cast(year(getdate()) as int)=cast(year(@var1) as int) and @var1<=@bztime and @var2>@bztime)
--介于标志时间之间,必须跨越当前数据库和历史数据库,从两表中取数据
begin
set @sqltmp = 'select * from backlsyygs.dbo.xsdetail' + ''+cast(year(@var1) as varchar(4)) +'' +' where sj>='+''''+convert(varchar(10),@var1,126) + ''''+' and sj<=' +'''';
set @sqltmp = @sqltmp +convert(varchar(10),@bztime,126)+'''';
set @sqltmp = @sqltmp + ' union all select * from lsyygs.dbo.xsdetail where sj>='+''''+convert(varchar(10),@bztime,126)+''''+' and sj<=' + ''''+convert(varchar(10),@var2,126)+'''';
end
if(@len<>0 and @var2>@bztime)--跨年,跨当前表
begin
set @sqltmp ='select * from lsyygs.dbo.xsdetail where sj>='+''''+convert(varchar(10),@bztime,126)+''''+' and sj<=' + ''''+convert(varchar(10),@var2,126)+'''';
declare @i int--计数器,计有多少个表
set @i=cast(year(@var2) as int )
while (@i>cast(year(@var1) as int ))
begin
set @sqltmp = @sqltmp + 'union all select * from backlsyyggs.dbo.xsdetail'+''+cast(@i as varchar(4)) +'';
set @i = @i-1
while (@i=cast(year(@var1) as int))
begin
set @sqltmp = @sqltmp + 'uinon all select * from backlsyygs.dbo.xsdetail where sj>='+''''+convert(varchar(10),@var1,126) + '''';
end
end
end
if (@len <> 0 and @var2 <=@bztime)--跨年,不跨当前表
begin
set @sqltmp = 'select * from backlsyygs.dbo.xsdetail' +'' + cast(year(@var2) as varchar(4)) + ''+' where sj<= ' +'''' + convert(varchar(10),@var2,126) + '''';
set @i=cast(year(@var2) as int )-1--减一是因为已经查了一个表
while (@i>cast(year(@var1) as int ))
begin
set @sqltmp = @sqltmp + 'union all select * from backlsyyggs.dbo.xsdetail'+''+cast(@i as varchar(4)) +'';
set @i=@i-1
end
while (@i=cast(year(@var1) as int))--最后一个表要加结束时间
set @sqltmp = @sqltmp + 'uinon all select * from backlsyygs.dbo.xsdetail where sj>='+''''+convert(varchar(10),@var1,126) + '''';
end
exec(@sqltmp)