在数据库中有表200701,200702,200703..........等等之类的日期表.里面的字段名和字段类型都是一致的.并且里面都有字段Insert_Time字段是DateTime类型.
列如:
我现在想找出2007-01-01到2008-01-01这个时间段的数据.
如何实现?????????????
各位帮帮我吧.
列如:
我现在想找出2007-01-01到2008-01-01这个时间段的数据.
如何实现?????????????
各位帮帮我吧.
Select * From [200701]
Where Insert_Time Between '2007-01-01' And '2008-01-01'
Union All
Select * From [200702]
Where Insert_Time Between '2007-01-01' And '2008-01-01'
Union All
Select * From [200703]
Where Insert_Time Between '2007-01-01' And '2008-01-01'
Select * From [200701]
Union All
Select * From [200702]
Union All
Select * From [200703]
) A
Where Insert_Time Between '2007-01-01' And '2008-01-01'
select @dt1='2007-01-01',@dt2='2008-01-01'
select * from @table
where dateadd(year,datediff(year,子段名,@dt1),字段名)
between @dt1 and @dt2
or dateadd(year,datediff(year,子段名,@dt2),字段名)
between @dt1 and @dt2
、 Between '2007-01-01' And '2008-01-01'
declare @starttime datetime,@endtime datetime
declare @years int, @months int
declare @startnextmonth varchar(30)
select @startnextmonth = cast(dateadd(mm,@starttime,1) as varchar(30))
select @startnextmonth = cast(year(@startnextmonth) as varchar(4))+cast(month(@startnextmonth) as varchar(2))+ '01'
select @months = datediff(mm,cast(@startnextmonth as datetime),@endtime)
declare @i int
set @i = 0
set @sql = ''
while(@i<@months)
begin
select @sql = @sql +' select * from '+ cast(year(@startnextmonth)as varchar(4)) + cast(right(cast(month(@startnextmonth)as varchar(2)),2) as varchar(2)) + 'union all'
set @startnextmonth = cast(dateadd(mm,startnextmonth,1) as varchar(30))
set @i =@i+1
end
set @sql = 'select * from '+ cast(year(@starttime)as varchar(4)) + cast(right(cast(month(@starttime)as varchar(2)),2) as varchar(2)) + ' where Insert_Time >= '+ cast(@startime as varchar(30)) + 'union all ' + @sql +
' select * from ' + cast(year(@endtime)as varchar(4)) + cast(right(cast(month(@endtime)as varchar(2)),2) as varchar(2)) + ' where Insert_Time <= '+ cast(@endtime as varchar(30))
exec(@sql)
declare @starttime datetime,@endtime datetime --傳入開始時間和結束時間
declare @years int, @months int
declare @startnextmonth varchar(30)
select @startnextmonth = cast(dateadd(mm,@starttime,1) as varchar(30))
select @startnextmonth = cast(year(@startnextmonth) as varchar(4))+cast(month(@startnextmonth) as varchar(2))+ '01' --得到開始時間的下一個月的 first day
select @months = datediff(mm,cast(@startnextmonth as datetime),@endtime) -- endtime 到 startme 的下月的firstday 總共計個月,也就是統計期間有多少個表
declare @i int
set @i = 0
set @sql = ''
while(@i<@months)
begin --拼接 @sql語句
select @sql = @sql +' select * from '+ cast(year(@startnextmonth)as varchar(4)) + cast(right(cast(month(@startnextmonth)as varchar(2)),2) as varchar(2)) + 'union all'
set @startnextmonth = cast(dateadd(mm,startnextmonth,1) as varchar(30))
set @i =@i+1
end
--再把startime 和 endtime 所在的表的數據 加上
set @sql = 'select * from '+ cast(year(@starttime)as varchar(4)) + cast(right(cast(month(@starttime)as varchar(2)),2) as varchar(2)) + ' where Insert_Time >= '+ cast(@startime as varchar(30)) + 'union all ' + @sql +
' select * from ' + cast(year(@endtime)as varchar(4)) + cast(right(cast(month(@endtime)as varchar(2)),2) as varchar(2)) + ' where Insert_Time <= '+ cast(@endtime as varchar(30))exec(@sql)
并且你可以事先创建好一直到2015年的所有的表,只不过都是空的,这样你这个视图就不用每新一个月就要修改了另外也可以使用参数视图,这样的效率最高