现在又一个表,假如叫table,里面有很多数据,都是一秒一个如:2011-01-06 21:44:06 0.50199997425079346 48.072998046875 16.0
2011-01-06 21:44:07 0.50199997425079346 48.072998046875 16.0
2011-01-06 21:44:08 0.50099998712539673 48.074001312255859 16.0
2011-01-06 21:44:09 0.50199997425079346 48.074001312255859 16.0
2011-01-06 21:44:10 0.49400001764297485 48.074001312255859 16.0
2011-01-06 21:44:11 0.49900001287460327 48.072998046875 16.0
2011-01-06 21:44:12 0.49700000882148743 48.075000762939453 16.0
2011-01-06 21:44:14 0.50199997425079346 48.074001312255859 16.0
……………………
2011-01-08 06:14:22 0.4869999885559082 48.100997924804687 16.899999618530273
2011-01-08 06:14:23 0.50099998712539673 48.110000610351563 16.899999618530273
2011-01-08 06:14:24 0.50199997425079346 48.110000610351563 16.899999618530273
2011-01-08 06:14:25 0.48800000548362732 48.104999542236328 16.899999618530273
2011-01-08 06:14:26 0.49700000882148743 48.102001190185547 16.899999618530273
2011-01-08 06:14:27 0.50599998235702515 48.104999542236328 16.899999618530273
2011-01-08 06:14:28 0.50900000333786011 48.104999542236328 16.899999618530273
现在想每隔一小时取一个数据!请问如何实现?
如:要查找出
2011-01-06 21:44:06
2011-01-06 22:44:06
2011-01-06 23:44:06
时间的数据……
2011-01-06 21:44:07 0.50199997425079346 48.072998046875 16.0
2011-01-06 21:44:08 0.50099998712539673 48.074001312255859 16.0
2011-01-06 21:44:09 0.50199997425079346 48.074001312255859 16.0
2011-01-06 21:44:10 0.49400001764297485 48.074001312255859 16.0
2011-01-06 21:44:11 0.49900001287460327 48.072998046875 16.0
2011-01-06 21:44:12 0.49700000882148743 48.075000762939453 16.0
2011-01-06 21:44:14 0.50199997425079346 48.074001312255859 16.0
……………………
2011-01-08 06:14:22 0.4869999885559082 48.100997924804687 16.899999618530273
2011-01-08 06:14:23 0.50099998712539673 48.110000610351563 16.899999618530273
2011-01-08 06:14:24 0.50199997425079346 48.110000610351563 16.899999618530273
2011-01-08 06:14:25 0.48800000548362732 48.104999542236328 16.899999618530273
2011-01-08 06:14:26 0.49700000882148743 48.102001190185547 16.899999618530273
2011-01-08 06:14:27 0.50599998235702515 48.104999542236328 16.899999618530273
2011-01-08 06:14:28 0.50900000333786011 48.104999542236328 16.899999618530273
现在想每隔一小时取一个数据!请问如何实现?
如:要查找出
2011-01-06 21:44:06
2011-01-06 22:44:06
2011-01-06 23:44:06
时间的数据……
--管理
--SQL Server代理
--右键作业
--新建作业
--"常规"项中输入作业名称
--"步骤"项
--新建
--"步骤名"中输入步骤名
--"类型"中选择"Transact-SQL 脚本(TSQL)"
--"数据库"选择执行命令的数据库
--"命令"中输入要执行的语句:
EXEC 存储过程名 ... --该存储过程用于创建表 --确定
--"调度"项
--新建调度
--"名称"中输入调度名称
--"调度类型"中选择你的作业执行安排
--如果选择"反复出现"
--点"更改"来设置你的时间安排
然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行 设置方法:
我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.
如在VC6中
CString strSQL;
strSQL=_T("Select ……………………");//SQL语句
m_pRecordset->Open(strSQL,m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
对了。
select min(时间) from table group by convert(varchar(13),时间,120)-- 2.
select * from table t where not exists (select 1 from table where convert(varchar(13),时间,120)=convert(varchar(13),t.时间,120) and 时间<t.时间)
(
[date] datetime,
value1 decimal(28, 17),
value2 decimal(28, 17),
value3 decimal(10, 1)
)
insert #
select '2011-01-06 21:44:06', 0.50199997425079346, 48.072998046875, 16.0 union all
select '2011-01-06 22:44:06', 0.50199997425079346, 48.072998046875, 16.0 union all
select '2011-01-06 23:44:06', 0.50099998712539673, 48.074001312255859, 16.0 union all
select '2011-01-07 00:44:06', 0.50199997425079346, 48.074001312255859, 16.0 union all
select '2011-01-07 01:44:07', 0.50199997425079346, 48.074001312255859, 16.0
select * from #--SQL
declare @StartDatetime datetime
set @StartDatetime = '2011-01-06 21:44:06'
select * from #
where DATEDIFF(second, @StartDatetime, [date])%3600 = 0
and [date] >= @StartDatetime
declare @StartDatetime datetime
set @StartDatetime = '2011-01-06 21:44:06'
select * from #
where DATEPART(second, @StartDatetime) = DATEPART(SECOND, [date])
and DATEPART(minute, @StartDatetime) = DATEPART(minute, [date])
and [date] >= @StartDatetime