DECLARE @c CHAR(6),@d1 datetime,@d2 DATETIME,@n INT,@sql VARCHAR(MAX)
SELECT @c='201408',@d1=CAST(@c+'01' AS DATETIME),@d2=DATEADD(m,1,@d1),@n=DATEDIFF(d,@d1,@d2),@sql='';WITH a1 AS
(
SELECT @d1 dDay,CONVERT(CHAR(8),@d1,112)+'_a' cDay,1 n
UNION ALL
SELECT dDay+1,CONVERT(CHAR(8),dDay+1,112)+'_a',n+1
FROM a1
WHERE n<@n
)
SELECT @sql=@sql+' union all select * from '+cDay
FROM a1EXEC(stuff(@sql,1,11,''))
SELECT @c='201408',@d1=CAST(@c+'01' AS DATETIME),@d2=DATEADD(m,1,@d1),@n=DATEDIFF(d,@d1,@d2),@sql='';WITH a1 AS
(
SELECT @d1 dDay,'['+CONVERT(CHAR(8),@d1,112)+'_a]' cDay,1 n
UNION ALL
SELECT dDay+1,'['+CONVERT(CHAR(8),dDay+1,112)+'_a]',n+1
FROM a1
WHERE n<@n
)
SELECT @sql=@sql+' union all select * from '+cDay
FROM a1
set @sql =stuff(@sql,1,11,'')
print @sql
EXEC(@sql)
其实就是一个拼接动态SQL问题,然后在拼接动态SQL后面加过滤条件就行;declare @start CHAR(6),@starttime datetime,@endtime datetime,@tablename varchar(10),@sql varchar(max)
select @start='201408',@tablename='',@sql='',@endtime=dateadd(day,-1,dateadd(m,1,cast(@start+'01' as datetime)))
select @starttime=cast(@start+'01' as datetime) while @starttime<=@endtime
begin
set @tablename=convert(varchar(10),@starttime,112)+'_a'
set @sql = @sql +'union all select * from '+@tablename+' '
set @starttime=dateadd(day,1,@starttime)
end
print stuff(@sql,1,10,'')