- SQL code
;with maco as ( select * from table1 union select * from table2)select a.datetime,b.temperature,c.temperature from maco a left join table1 b on a.[datetime]=b.[datetime]left join table2 c on a.[datetime]=c.[datetime]
我想写成存储过程，把table1、table2表名传入，请问这个该怎么做？
如果那个不好改，改下面这个也可以的，谢谢啦
- SQL code
SELECT * FROM (SELECT *,1 AS value FROM table1UNION ALLSELECT *,2 FROM table2)aPIVOT (MAX([temperature]) FOR value IN ([1],[2]))b
------解决方案--------------------
- SQL code
--不同的表，字段会不一样--把表名做参数，如果表没有[TEMPERATURE]字段会报错的。CREATE PROC PROCNAME( @TAB1 VARCHAR(50), @TAB2 VARCHAR(50),)ASBEGIN DECLARE @SQL VARCHAR(MAX) SET @SQL=' SELECT * FROM ( SELECT *,1 AS VALUE FROM [email protected]+' UNION ALL SELECT *,2 FROM [email protected]+' ) A PIVOT ( MAX([TEMPERATURE]) FOR VALUE IN ([1], [2])) B' EXEC (@SQL)END
------解决方案--------------------
create proc a
(
@table1 varchar(100),
@table2 varchar(100)
)
as
begin
declare @sql varchar(4000) =
';with maco as
(
select * from @table1
union
select * from @table2
)
select a.datetime,b.temperature,c.temperature from maco a
left join table1 b on a.[datetime]=b.[datetime]
left join table2 c on a.[datetime]=c.[datetime]
'
set @sql = REPLACE( @sql,[email protected]',@table1)
set @sql = REPLACE( @sql,[email protected]',@table2)
exec (@sql)
end
------解决方案--------------------
- SQL code
DECLARE @table_name VARCHAR(1000)SET @table_name='tb1,tb2,tb3,tbn,tbm'DECLARE @t TABLE(id INT IDENTITY(1,1),name VARCHAR(50))DECLARE @x XML ,@row_cnt INT ,@i INT ,@sql VARCHAR(MAX),@sql2 VARCHAR(MAX)SET @i=1SET @x=CONVERT(XML,'<x>'+REPLACE(@table_name, ',' , '</x><x>')+'</x>')INSERT INTO @tSELECT N.v.value('.' , 'varchar(50)')FROM @x.nodes('/x') N (v)SELECT @sql=ISNULL(@sql+CHAR(10)+'union all'+CHAR(10),'')+'select *,'+LTRIM(id)+' as id from '+QUOTENAME(name) FROM @tSELECT @sql2= ISNULL(@sql2+',','')+QUOTENAME(id)FROM @tSELECT @sql='SELECT * FROM([email protected]+') ta PIVOT ( MAX([TEMPERATURE]) FOR VALUE IN ([email protected]+')) tb'--PRINT @sqlEXEC(@sql)