表 a
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表的最后一行
最好能写个详细的注释,谢谢啦
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表的最后一行
最好能写个详细的注释,谢谢啦
解决方案 »
- 在Listview中某个字段的查询语句
- 这个SQL如何优化?
- 数据库的总结与设计
- SQL语句的基础问题,急急! 欢迎各位高手请进,谢谢指教.
- SQL Server2005 捕获错误问题
- 救救我好吗??谢谢您了
- 我用asp.net读取文件中的sql语句给sqlserver执行,很容易语法错误(本身没有错的)
- 大虾,介绍3本DELPHI数据库(MS SQL SERVER)编程书,有下载吗?
- insert 触发器
- 数据系统作业调度的具体过程
- 用sp_addlinkedserver添加了连接服务器,那在存储过程中的事物,该怎么写呢!以前的写法,去都报错!
- 请高手解决问题,不然国庆后就会被炒鱿鱼咯 555555555
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 numeric(24,16),Val3 numeric(24,16),Val4 numeric(24,16))
goinsert into b
select
datepart(hh,t.DateAndTime),
(select top 1 Val from a where datepart(hh,t.DateAndTime)=datepart(hh,DateAndTime) and TagIndex=1 order by DateAndTime desc),
(select top 1 Val from a where datepart(hh,t.DateAndTime)=datepart(hh,DateAndTime) and TagIndex=3 order by DateAndTime desc),
(select top 1 Val from a where datepart(hh,t.DateAndTime)=datepart(hh,DateAndTime) and TagIndex=4 order by DateAndTime desc)
from
a t
group by
datepart(hh,t.DateAndTime)select * from b
go
drop table a,b
val3=sum(case tagindex when 3 then val else 0 end) ,
val4=sum(case tagindex when 4 then val else 0 end)
from tableA a where not exists (select 1 from tableA b where a.tagindex=b.tagindex and convert(varchar(13),a.dateandtime,120)=convert(varchar(13),b.dateandtime,120) and a.dateandtime<b.dateandtime)
group by datepart(hour,dateandtime)
sum(case TagIndex 3 then Val end) as Val3, sum(case TagIndex 4 then Val end) as Val4
from tableA a
join (select datepear(Hour, DateAndTime) as TheHour, max(DateAndTime) as MaxDateAndTime
from tableA
group by datepear(Hour, DateAndTime) b on a.DateAndTime = b.MaxDateAndTime
group by b.TheHour, b.MaxDateAndTime
create table a(DateAndTime datetime,TagIndex int,Val numeric(24,16))
create table b(DateAndTime int,Val1 numeric(24,16),Val3 numeric(24,16),Val4 numeric(24,16))
go--向a表insert测试数据
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
go
--执行insert查询
insert into b(DateAndTime,Val1,Val3,Val4)
select
datepart(hh,a.DateAndTime),
max(case a.TagIndex when 1 then a.Val end),
max(case a.TagIndex when 3 then a.Val end),
max(case a.TagIndex when 4 then a.Val end)
from
a,
(select max(DateAndTime) as DateAndTime from a group by convert(char(13),DateAndTime,120)) c
where
a.DateAndTime=c.DateAndTime
group by
datepart(hh,a.DateAndTime)
go
--查看结果
select * from b
go
--清除测试环境
drop table a,b
------------------------------------------------------------------------------------
每一个查询前加上这一句即可:
insert into b(DateAndTime,Val1,Val3,Val4)
select datepart(hour,dateandtime),val1=sum(case tagindex when 1 then val else 0 end) ,
val3=sum(case tagindex when 3 then val else 0 end) ,
val4=sum(case tagindex when 4 then val else 0 end)
from tableA a where not exists (select 1 from tableA b where a.tagindex=b.tagindex and convert(varchar(13),a.dateandtime,120)=convert(varchar(13),b.dateandtime,120) and a.dateandtime<b.dateandtime)
group by datepart(hour,dateandtime)
insert into a select '2006-10-8 15:51:00',1,11113367.258
insert into a select '2006-10-8 15:51:00',2,0.253678923
insert into a select '2006-10-8 15:51:00',3,1125.68792
insert into a select '2006-10-8 15:51:00',4,1
insert into a select '2006-10-8 15:59:00',1,11115367.258
insert into a select '2006-10-8 15:59:00',2,0.2536789
insert into a select '2006-10-8 15:59:00',3,1125.687921566458
insert into a select '2006-10-8 15:59:00',4,0
insert into a select '2006-10-8 16:59:00',1,11158967.586
insert into a select '2006-10-8 16:59:00',2,0.2576545454
insert into a select '2006-10-8 16:59:00',3,1125.45468784565456
insert into a select '2006-10-8 16:59:00',4,1
create table b(DateAndTime int,Val1 numeric(24,16),Val3 numeric(24,16),Val4 numeric(24,16))
goinsert into b(DateAndTime,Val1,Val3,Val4)
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 top 1 Val from a where datediff(hh,DateAndTime,getdate())=1 and TagIndex=4 order by DateAndTime desc)select * from b
go
drop table a,b
create table a(DateAndTime datetime,TagIndex int,Val numeric(24,16))
insert into a select '2006-10-8 15:51:00',1,11113367.258
insert into a select '2006-10-8 15:51:00',2,0.253678923
insert into a select '2006-10-8 15:51:00',3,1125.68792
insert into a select '2006-10-8 15:51:00',4,1
insert into a select '2006-10-8 15:59:00',1,11115367.258
insert into a select '2006-10-8 15:59:00',2,0.2536789
insert into a select '2006-10-8 15:59:00',3,1125.687921566458
insert into a select '2006-10-8 15:59:00',4,0
insert into a select '2006-10-8 16:59:00',1,11158967.586
insert into a select '2006-10-8 16:59:00',2,0.2576545454
insert into a select '2006-10-8 16:59:00',3,1125.45468784565456
insert into a select '2006-10-8 16:59:00',4,1
create table b(DateAndTime int,Val1 numeric(24,16),Val3 numeric(24,16),Val4 numeric(24,16))
goinsert into b(DateAndTime,Val1,Val3,Val4)
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 top 1 Val from a where datediff(hh,DateAndTime,getdate())=1 and TagIndex=4 order by DateAndTime desc)select * from b
go
drop table a,b
-------------------------------------------------------------------------------------------------------------------------------
--创建测试表结构
create table a(DateAndTime datetime,TagIndex int,Val numeric(24,16))
create table b(DateAndTime int,Val1 numeric(24,16),Val3 numeric(24,16),Val4 numeric(24,16))
go--向a表insert测试数据
insert into a select '2006-10-8 15:51:00',1,11113367.258
insert into a select '2006-10-8 15:51:00',2,0.253678923
insert into a select '2006-10-8 15:51:00',3,1125.68792
insert into a select '2006-10-8 15:51:00',4,1
insert into a select '2006-10-8 15:59:00',1,11115367.258
insert into a select '2006-10-8 15:59:00',2,0.2536789
insert into a select '2006-10-8 15:59:00',3,1125.687921566458
insert into a select '2006-10-8 15:59:00',4,0
insert into a select '2006-10-8 16:59:00',1,11158967.586
insert into a select '2006-10-8 16:59:00',2,0.2576545454
insert into a select '2006-10-8 16:59:00',3,1125.45468784565456
insert into a select '2006-10-8 16:59:00',4,1
go
--执行insert查询
insert into b(DateAndTime,Val1,Val3,Val4)
select
datepart(hh,a.DateAndTime),
max(case a.TagIndex when 1 then a.Val end),
max(case a.TagIndex when 3 then a.Val end),
max(case a.TagIndex when 4 then a.Val end)
from
a,
(select max(DateAndTime) as DateAndTime from a group by convert(char(13),DateAndTime,120)) c
where
a.DateAndTime=c.DateAndTime
and
datediff(hh,a.DateAndTime,getdate())=1
group by
datepart(hh,a.DateAndTime)
go
--查看结果
select * from b
go
--清除测试环境
drop table a,b
我的想法是,每个小时写入一行数据,每天00:00:00到1:00:00输出该表并清空该表
------------------------------------------------------------------------------------------------------------------------
上面两个帖子是修改后的,应该可以满足你的要求。
另外,要实现在"每天00:00:00到1:00:00输出该表并清空该表",需要使用JOB。不过不明白这个输出是个什么操作?
子陌哥哥,最后实际写到作业里面用
insert into b(DateAndTime,Val1,Val3,Val4)
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 top 1 Val from a where datediff(hh,DateAndTime,getdate())=1 and TagIndex=4 order by DateAndTime desc)select * from b
go
这个就可以吧?
还有要是一次不是写3行而是10多行也没问题吧?
顺便问候:
libin_ftsafe(子陌红尘:当libin告别ftsafe) ( )
!!!
--管理
--SQL Server代理
--右键作业
--新建作业
--"常规"项中输入作业名称
--"步骤"项
--新建
--"步骤名"中输入步骤名
--"类型"中选择"Transact-SQL 脚本(TSQL)"
--"数据库"选择执行命令的数据库
--"命令"中输入要执行的语句:
TRANCATE TABLE a --清空a表的数据 --确定
--"调度"项
--新建调度
--"名称"中输入调度名称
--"调度类型"中选择你的作业执行安排
--如果选择"反复出现"
--点"更改"来设置你的时间安排
然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行设置方法:
我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.
还有要是一次不是写3行而是10多行也没问题吧?
-----------------------------------------------------------------------------
可以&可以。
create table testTime
(
DateAndTime datetime ,
TagIndex int ,
val real
)insert into testTime
select '2006-10-6 15:51:00' , 1 , 11113367.258 union
select '2006-10-6 15:51:00' , 2 , 0.253678923 union
select '2006-10-6 15:51:00' , 3 , 1125.68792 union
select '2006-10-6 15:51:00' , 4 , 1 union
select '2006-10-6 15:59:00' , 1 , 11115367.258 union
select '2006-10-6 15:59:00' , 2 , 0.2536789 union
select '2006-10-6 15:59:00' , 3 , 1125.687921566458 union
select '2006-10-6 15:59:00' , 4 , 0 union
select '2006-10-6 16:59:00' , 1 , 11158967.586 union
select '2006-10-6 16:59:00' , 2 , 0.2576545454 union
select '2006-10-6 16:59:00' , 3 , 1125.45468784565456 union
select '2006-10-6 16:59:00' , 4 , 1--显示测试数据
select * from testtime/*
2006-10-06 15:51:00.000 1 1.1113367E+7
2006-10-06 15:51:00.000 2 0.25367892
2006-10-06 15:51:00.000 3 1125.6879
2006-10-06 15:51:00.000 4 1.0
2006-10-06 15:59:00.000 1 1.1115367E+7
2006-10-06 15:59:00.000 2 0.25367889
2006-10-06 15:59:00.000 3 1125.6879
2006-10-06 15:59:00.000 4 0.0
2006-10-06 16:59:00.000 1 1.1158968E+7
2006-10-06 16:59:00.000 2 0.25765455
2006-10-06 16:59:00.000 3 1125.4547
2006-10-06 16:59:00.000 4 1.0
*/--生成结果表
create table testTimeRlt
(
DateAndTime varchar(2) ,
Val1 real ,
Val2 real ,
Val3 real ,
)--执行查询 并且插入数据表
--这里声明了个时间变量 如果符合当前时间
--声明可以去掉 , 直接用 getdate() 代替就可以了
declare @nowTime datetime
set @nowTime = '2006-10-6 16:00:00'insert into testTimeRlt
select
convert(varchar(2) , DateAndTime , 114) as DateAndTime ,
max(case when TagIndex = 1 then val end) as Val1,
max(case when TagIndex = 3 then val end) as Val3,
max(case when TagIndex = 4 then val end) as Val4
from testTime
where DateAndTime =
(
select max(DateAndTime) as DateAndTime
from testTime
where DateAndTime < @nowTime and DateAndTime >= dateadd(Hour , -1 ,@nowTime )
)
group by DateAndTime
--察看结果
select * from testTimeRlt/*
DateAndTime Val1 Val3 Val4
----------------------------------------------------
15 1.1115367E+7 1125.6879 0.0
*/
val1=sum(case a.tagindex when 1 then val else 0 end) ,
val3=sum(case a.tagindex when 3 then val else 0 end) ,
val4=sum(case a.tagindex when 4 then val else 0 end)
(select to_date(DateAndTime,'hh') DateAndTime,
TagIndex,
Val,
row_number() over (partition by to_date(DateAndTime,'hh'),TagIndex order by val desc) val_row
from tableA)
where val_row = '1'
create table testTime
(
DateAndTime datetime ,
TagIndex int ,
val real
)insert into testTime
select '2006-10-6 15:51:00' , 1 , 11113367.258 union
select '2006-10-6 15:51:00' , 2 , 0.253678923 union
select '2006-10-6 15:51:00' , 3 , 1125.68792 union
select '2006-10-6 15:51:00' , 4 , 1 union
select '2006-10-6 15:59:00' , 1 , 11115367.258 union
select '2006-10-6 15:59:00' , 2 , 0.2536789 union
select '2006-10-6 15:59:00' , 3 , 1125.687921566458 union
select '2006-10-6 15:59:00' , 4 , 0 union
select '2006-10-6 16:59:00' , 1 , 11158967.586 union
select '2006-10-6 16:59:00' , 2 , 0.2576545454 union
select '2006-10-6 16:59:00' , 3 , 1125.45468784565456 union
select '2006-10-6 16:59:00' , 4 , 1
顺便问候:
libin_ftsafe(子陌红尘:当libin告别ftsafe) ( ) 真的了不起拉.我刚进来不久只有学习的份.
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))
goselect * from #a
/*
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
*/
insert #b
select datepart(hour,b.DateandTime),b.Val,c.Val,d.Val from(select max(convert(varchar(16),DateandTime,120)) max_date from #a group by convert(varchar(13),DateandTime,120)) a join #a b on max_date=b.DateandTime and b.TagIndex=1 join #a c on max_date=c.DateandTime and c.TagIndex=3 join #a d on max_date=d.DateandTime and d.TagIndex=4select * from #b
/*
DateAndTime Val1 Val3 Val4
15 11115367.258 1125.687921566458 0
16 11158967.586 1125.45468784565456 1
*/
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))
goselect * from #a
/*
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
*/
insert #b
select datepart(hour,DateandTime),val1=max(case tagindex when 1 then val end) ,
val3=max(case tagindex when 3 then val end) ,
val4=max(case tagindex when 4 then val end)
from #a join (select max(DateandTime) max_date from #a) b on max_date=DateandTime
group by datepart(hour,DateandTime)select * from #b
/*
DateAndTime Val1 Val3 Val4
16 11158967.586 1125.45468784565456 1
*/