insert into b(DateAndTime,Val1,Val3)
select
datepart(hh,dateadd(hh,-1,getdate())),
(select top 1 Val from a where datediff(hh,DateAndTime,getdate())=1 and TagIndex=1 order by DateAndTime desc),
(select top 1 Val from a where datediff(hh,DateAndTime,getdate())=1 and TagIndex=3 order by DateAndTime desc)
select * from b
go
想把最大时间改成选取两个数值之和最大值的哪次
select
datepart(hh,dateadd(hh,-1,getdate())),
(select top 1 Val from a where datediff(hh,DateAndTime,getdate())=1 and TagIndex=1 order by DateAndTime desc),
(select top 1 Val from a where datediff(hh,DateAndTime,getdate())=1 and TagIndex=3 order by DateAndTime desc)
select * from b
go
想把最大时间改成选取两个数值之和最大值的哪次
datepart(hh,dateadd(hh,-1,getdate())) --返回的是int型
DateAndTime TagIndex Val
2006-10-6 15:51:00 1 11113367.258
2006-10-6 15:51:00 2 0.253678923
2006-10-6 15:51:00 3 1125.68792
2006-10-6 15:51:00 4 1
2006-10-6 15:59:00 1 11115367.258
2006-10-6 15:59:00 2 0.2536789
2006-10-6 15:59:00 3 1125.687921566458
2006-10-6 15:59:00 4 0
2006-10-6 16:59:00 1 11158967.586
2006-10-6 16:59:00 2 0.2576545454
2006-10-6 16:59:00 3 1125.45468784565456
2006-10-6 16:59:00 4 1
表b
DateAndTime Val1 Val3 Val4
15 11115367.258 1125.687921566458 0
16 11158967.586 1125.45468784565456 1
想实现的是这样的语句
要求:
条件1:读取上个小时的表a的TagIndex列的1.3.4行的Val列的数据对应写到表b的Val1,Val3,Val4
条件2:假如上个小时有多次数据,只选取和值最大的那次.
条件3:并且把小时的数值写到表b的DateAndTime列上或者用别的办法把小时数写进去也可以
条件4:添加到b表的最后一行
是这个:)
drop table #a
if exists(select * from tempdb..sysobjects where object_id('tempdb..#b')=id)
drop table #b
gocreate table #a(DateandTime datetime,TagIndex int,Val varchar(20))
insert into #a select '2006-10-6 15:51:00',1,11113367.258
insert into #a select '2006-10-6 15:51:00',2,0.253678923
insert into #a select '2006-10-6 15:51:00',3,1125.68792
insert into #a select '2006-10-6 15:51:00',4,1
insert into #a select '2006-10-6 15:59:00',1,11115367.258
insert into #a select '2006-10-6 15:59:00',2,0.2536789
insert into #a select '2006-10-6 15:59:00',3,1125.687921566458
insert into #a select '2006-10-6 15:59:00',4,0
insert into #a select '2006-10-6 16:59:00',1,11158967.586
insert into #a select '2006-10-6 16:59:00',2,0.2576545454
insert into #a select '2006-10-6 16:59:00',3,1125.45468784565456
insert into #a select '2006-10-6 16:59:00',4,1
create table #b(DateandTime int,Val1 varchar(20),Val3 varchar(20),Val4 varchar(20))
go
a表偶尔会有多次数据,想取出来数值之合最大的那次
他是工控机出的数据吗,所以每次重新启动都会有个初始化的动作,这个时候读取的数值是不完整的.
drop table #a
if exists(select * from tempdb..sysobjects where object_id('tempdb..#b')=id)
drop table #b
gocreate table #a(DateandTime datetime,TagIndex int,Val varchar(20))
insert into #a select '2006-12-16 8:51:00',1,11113367.258
insert into #a select '2006-12-16 8:51:00',2,0.253678923
insert into #a select '2006-12-16 8:51:00',3,1125.68792
insert into #a select '2006-12-16 8:51:00',4,1
insert into #a select '2006-12-16 9:30:00',1,11115367.258
insert into #a select '2006-12-16 9:30:00',2,0.2536789
insert into #a select '2006-12-16 9:30:00',3,1125.687921566458
insert into #a select '2006-12-16 9:30:00',4,0
insert into #a select '2006-12-16 9:35:00',1,11158967.586
insert into #a select '2006-12-16 9:35:00',2,0.2576545454
insert into #a select '2006-12-16 9:35:00',3,1125.45468784565456
insert into #a select '2006-12-16 9:35:00',4,1
create table #b(DateandTime int,Val1 varchar(20),Val3 varchar(20),Val4 varchar(20))
go
这个比较符合实际查询的需求,谁好心帮帮我
select
datepart(hh,dateadd(hh,-1,getdate())),
(select max(Val) from #a where datediff(hh, DateAndTime, getdate())=1 and TagIndex=1),
(select max(Val) from #a where datediff(hh, DateAndTime, getdate())=1 and TagIndex=3),
(select max(Val) from #a where datediff(hh, DateAndTime, getdate())=1 and TagIndex=4)
将 varchar 值 '11113367.258' 转换为数据类型为 int 的列时发生语法错误。这个错误...
这样好麻烦的