表1
Declare @sql varchar(8000)
set @sql ='select tagtime as 时间,iStation as 机号'
select @sql=@sql+',sum(case tagname when '''+tagname+''' then tagvalue else 0 end) ['+tagname+']'
from (select distinct tagname from tbReport) as tbReport
Select @sql = @sql+' from tbReport group by tagtime,istation'
Exec (@sql)
表2
select
CHAR(SUBSTRING(a.strsn,1,5)%256) +CHAR(SUBSTRING(a.strsn,1,5)/256)+
CHAR(SUBSTRING(a.strsn,6,5)%256)+CHAR(SUBSTRING(a.strsn,6,5)/256)+
CHAR(SUBSTRING(a.strsn,11,5)%256)+CHAR(SUBSTRING(a.strsn,11,5)/256)+
CHAR(SUBSTRING(a.strsn,16,5)%256) +CHAR(SUBSTRING(a.strsn,26,5)/256)+
CHAR(SUBSTRING(a.strsn,21,5)%256)+CHAR(SUBSTRING(a.strsn,21,5)/256)+
CHAR(SUBSTRING(a.strsn,26,5)%256) +CHAR(SUBSTRING(a.strsn,26,5)/256)+
CHAR(SUBSTRING(a.strsn,31,5)%256) +CHAR(SUBSTRING(a.strsn,31,5)/256)+
CHAR(SUBSTRING(a.strsn,36,5)%256)+CHAR(SUBSTRING(a.strsn,36,5)/256)+
CHAR(SUBSTRING(a.strsn,41,5)%256)+CHAR(SUBSTRING(a.strsn,41,5)/256) AS 条码,a.* from XBDB.dbo.tbBarcode a
两个表都是通过语句查询出来的,其中 表2.tagtime=表1.时间,我想将两个表关联,不需要建立两个表
------解决方案--------------------
Declare @sql varchar(8000)
set @sql ='select * from(select tagtime as 时间,iStation as 机号'
select @sql=@sql+',sum(case tagname when '''+tagname+''' then tagvalue else 0 end) ['+tagname+']'
from (select distinct tagname from tbReport) as tbReport
Select @sql = @sql+' from tbReport group by tagtime,istation) m join
(select
CHAR(SUBSTRING(a.strsn,1,5)%256) +CHAR(SUBSTRING(a.strsn,1,5)/256)+
CHAR(SUBSTRING(a.strsn,6,5)%256)+CHAR(SUBSTRING(a.strsn,6,5)/256)+
CHAR(SUBSTRING(a.strsn,11,5)%256)+CHAR(SUBSTRING(a.strsn,11,5)/256)+
CHAR(SUBSTRING(a.strsn,16,5)%256) +CHAR(SUBSTRING(a.strsn,26,5)/256)+
CHAR(SUBSTRING(a.strsn,21,5)%256)+CHAR(SUBSTRING(a.strsn,21,5)/256)+
CHAR(SUBSTRING(a.strsn,26,5)%256) +CHAR(SUBSTRING(a.strsn,26,5)/256)+
CHAR(SUBSTRING(a.strsn,31,5)%256) +CHAR(SUBSTRING(a.strsn,31,5)/256)+
CHAR(SUBSTRING(a.strsn,36,5)%256)+CHAR(SUBSTRING(a.strsn,36,5)/256)+
CHAR(SUBSTRING(a.strsn,41,5)%256)+CHAR(SUBSTRING(a.strsn,41,5)/256) AS 条码,a.* from XBDB.dbo.tbBarcode a) n
on m.时间=n.tagtime'
Exec (@sql)