declare @sql varchar(1000) set @sql =''select @sql = @sql + result from ( select 'select * from ' + name + ' union ' as result from sys.objects where name like 'P%' and type = 'U')t1select @sql = LEFT(@sql, LEN(@sql) - LEN(' union '))exec (@sql)
是这样的 比如用一个存储过程查出了N个需要的表、table1,table2,table3,table4,table5 就算这5个 怎么动态的拼接成: select * from table1 union all select * from table2 union all select * from table3 union all select * from table4 union all select * from table5 这是表是要经常变的、、
CREATE TABLE P20120625 ( ID INT ) GO CREATE TABLE P20120624 ( ID INT ) GO CREATE TABLE P20120623 ( ID INT ) GODECLARE @Begin VARCHAR(10) --开始日期 DECLARE @End VARCHAR(10) --结束日期 DECLARE @Sql VARCHAR(8000)SET @Begin = '20120622' SET @End = '20120626' SET @Sql = ''SELECT @Sql = @Sql + 'SELECT * FROM ' + name + ' UNION ' FROM SYSOBJECTS WHERE xtype = 'U' AND name BETWEEN 'P' + @Begin AND 'P' + @EndSET @Sql = LEFT(@Sql,LEN(@Sql) - 5)EXEC(@Sql)
select convert(varchar(8),GETDATE(),112) --将时间格式转换 e.g. 2012-06-21 -->20120621declare @datefrom date--时间格式 declare @dateto date--时间格式 declare @sql_qry varchar(max) set @datefrom='2012-06-21' set @dateto='2012-06-26' print convert(varchar(8),@datefrom,112) set @sql_qry=' select * from P'+convert(varchar(8),@datefrom,112)while convert(varchar(8),@datefrom,112)<convert(varchar(8),@dateto,112) begin set @datefrom=DATEADD(DD,1,@datefrom) set @sql_qry=@sql_qry+' union all select * from P'+convert(varchar(8),@datefrom,112) end print @sql_qry EXEC(@sql_qry)--结果 /* (1 row(s) affected) 20120621 select * from P20120621 union all select * from P20120622 union all select * from P20120623 union all select * from P20120624 union all select * from P20120625 union all select * from P20120626 */
这样拼接出来是对的、怎么能看到结果呢、是最终查询得到的结果、不是 select * from P20120621 union all select * from P20120622 这样的
set @sql =''select @sql = @sql + result from (
select 'select * from ' + name + ' union ' as result
from sys.objects
where name like 'P%'
and type = 'U')t1select @sql = LEFT(@sql, LEN(@sql) - LEN(' union '))exec (@sql)
怎么动态的拼接成:
select * from table1
union all
select * from table2
union all
select * from table3
union all
select * from table4
union all
select * from table5
这是表是要经常变的、、
CREATE TABLE P20120625
(
ID INT
)
GO
CREATE TABLE P20120624
(
ID INT
)
GO
CREATE TABLE P20120623
(
ID INT
)
GODECLARE @Begin VARCHAR(10) --开始日期
DECLARE @End VARCHAR(10) --结束日期
DECLARE @Sql VARCHAR(8000)SET @Begin = '20120622'
SET @End = '20120626'
SET @Sql = ''SELECT @Sql = @Sql + 'SELECT * FROM ' + name + ' UNION '
FROM SYSOBJECTS WHERE xtype = 'U' AND name BETWEEN 'P' + @Begin AND 'P' + @EndSET @Sql = LEFT(@Sql,LEN(@Sql) - 5)EXEC(@Sql)
declare @dateto date--时间格式
declare @sql_qry varchar(max)
set @datefrom='2012-06-21'
set @dateto='2012-06-26'
print convert(varchar(8),@datefrom,112)
set @sql_qry=' select * from P'+convert(varchar(8),@datefrom,112)while convert(varchar(8),@datefrom,112)<convert(varchar(8),@dateto,112)
begin
set @datefrom=DATEADD(DD,1,@datefrom)
set @sql_qry=@sql_qry+'
union all
select * from P'+convert(varchar(8),@datefrom,112)
end
print @sql_qry
EXEC(@sql_qry)--结果
/*
(1 row(s) affected)
20120621
select * from P20120621
union all
select * from P20120622
union all
select * from P20120623
union all
select * from P20120624
union all
select * from P20120625
union all
select * from P20120626
*/
select * from P20120621
union all
select * from P20120622 这样的
他那只是Print出來的結果,Exec( xxx )就會執行.