求SQL语句，取两条记录间隔5秒以上的数据集

2015-07-17 04:35:32 627 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

ORACLE的SQL练习---2. 实现15分钟一个间隔的记录统计有如下一张记录表，如何查询出每隔15分钟的记录数，例如电商网站统计一个间隔的交易数等等…… 预期结果如下： --建表语句 --创建练习表 create table LX_02 ( id NUMBER, inputtime DATE ); --插入数据 insert into lx_02 (ID, INPUTTIME) values (1, to_date('14-01-2020 09:07:23', 'dd-mm-yyyy hh24:mi:ss')); commit; ins 阅读详情

表结构
id datetime value
1 2015-05-05 08:00:00.000 0
2 2015-05-05 08:00:01.000 1
3 2015-05-05 08:00:02.000 2
4 2015-05-05 08:00:03.000 3
5 2015-05-05 08:00:04.000 4
6 2015-05-05 08:00:05.000 5
7 2015-05-05 08:00:06.000 6
8 2015-05-05 08:00:07.000 7
9 2015-05-05 08:00:08.000 8
10 2015-05-05 08:00:09.000 9
11 2015-05-05 08:00:10.000 10
12 2015-05-05 08:00:15.000 15
13 2015-05-05 08:00:19.000 19
14 2015-05-05 08:00:20.000 20
15 2015-05-05 08:00:30.000 30
16 2015-05-05 08:00:31.000 31
17 2015-05-05 08:00:36.000 36

要求取的数据之间间隔5秒以上
结果应该是这样
1 2015-05-05 08:00:00.000 0
6 2015-05-05 08:00:05.000 5
11 2015-05-05 08:00:10.000 10
12 2015-05-05 08:00:15.000 15
14 2015-05-05 08:00:20.000 20
15 2015-05-05 08:00:30.000 30
17 2015-05-05 08:00:36.000 36

后一个数据与前一个数据的间隔应该在5秒以上，得到这个数据后，再以这个数据为基础，再往后间隔5秒以上的第一个数据，依次类推。

...全文

在SQL Server中建立定时任务，处理时间超过一天的记录这个不能用触发器，要用作业。（转贴）打开企业管理器，进入你服务器名下的“管理”，启动SQL Server代理。然后查看服务器属性，选中“自动启动SQL Server代理”。接下来进入SQL Server代理下的“作业”，在右边点右键选“新建作业”。在“常规”里，随便输入一个作业名，分类要选最后一项“数据库维护”。在“步骤”里，点“新建步骤”，随便输入一个步骤名，数据库选成你的数据库，命令阅读详情

wangji666666

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

wangji666666 2015-07-17

打赏
举报

为了验证各位答案的正确性，我加了2行数据 Id DT Value 1 2015-05-05 08:00:00.000 0 2 2015-05-05 08:00:01.000 1 3 2015-05-05 08:00:02.000 2 4 2015-05-05 08:00:03.000 3 5 2015-05-05 08:00:04.000 4 6 2015-05-05 08:00:05.000 5 7 2015-05-05 08:00:06.000 6 8 2015-05-05 08:00:07.000 7 9 2015-05-05 08:00:08.000 8 10 2015-05-05 08:00:09.000 9 11 2015-05-05 08:00:10.000 10 12 2015-05-05 08:00:15.000 15 13 2015-05-05 08:00:19.000 19 14 2015-05-05 08:00:20.000 20 15 2015-05-05 08:00:30.000 30 16 2015-05-05 08:00:31.000 31 17 2015-05-05 08:00:36.000 36 18 2015-05-05 08:00:40.000 40 19 2015-05-05 08:00:41.000 41 按照题意得到的结果应该是： Id DT Value 1 2015-05-05 08:00:00.000 0 6 2015-05-05 08:00:05.000 5 11 2015-05-05 08:00:10.000 10 12 2015-05-05 08:00:15.000 15 14 2015-05-05 08:00:20.000 20 15 2015-05-05 08:00:30.000 30 17 2015-05-05 08:00:36.000 36 19 2015-05-05 08:00:41.000 41 @reenjie和@Landa_Alice执行的结果： 1 2015-05-05 08:00:00.000 0 6 2015-05-05 08:00:05.000 5 11 2015-05-05 08:00:10.000 10 12 2015-05-05 08:00:15.000 15 14 2015-05-05 08:00:20.000 20 15 2015-05-05 08:00:30.000 30 17 2015-05-05 08:00:36.000 36 18 2015-05-05 08:00:40.000 40 @Cherise_huang执行的结果是： Id DT Value 1 2015-05-05 08:00:00.000 0 6 2015-05-05 08:00:05.000 5 11 2015-05-05 08:00:10.000 10 12 2015-05-05 08:00:15.000 15 14 2015-05-05 08:00:20.000 20 15 2015-05-05 08:00:30.000 30 17 2015-05-05 08:00:36.000 36 19 2015-05-05 08:00:41.000 41 所以Cherise_huang同志的答案是最符合题意的正确答案。谢谢大家的帮助！

Cherise_huang 2015-07-17

打赏
举报



create table dates(id INT,[DATETIME] DATETIME,VALUE int)

insert into dates 

select '1', '2015-05-05 08:00:00.000', '0' UNION all

select '2', '2015-05-05 08:00:01.000', '1' UNION all

select '3', '2015-05-05 08:00:02.000' ,'2' UNION all

select '4', '2015-05-05 08:00:03.000' ,'3' UNION all

select '5', '2015-05-05 08:00:04.000' ,'4' UNION all

select '6', '2015-05-05 08:00:05.000' ,'5' UNION all

select '7','2015-05-05 08:00:06.000' ,'6' UNION all

select '8', '2015-05-05 08:00:07.000' ,'7' UNION all

select '9', '2015-05-05 08:00:08.000', '8' UNION all

select '10', '2015-05-05 08:00:09.000' ,'9' UNION all

select '11', '2015-05-05 08:00:10.000' ,'10' UNION all

select'12', '2015-05-05 08:00:15.000' ,'15' UNION all

select '13', '2015-05-05 08:00:19.000' ,'19' UNION all

select'14', '2015-05-05 08:00:20.000' ,'20' UNION all

select'15', '2015-05-05 08:00:30.000' ,'30' UNION all

select'16','2015-05-05 08:00:31.000','31' UNION all

select'17', '2015-05-05 08:00:36.000','36'



DECLARE @Temp20150717 TABLE (DatetimeS DATETIME)



DECLARE @Sdate DATETIME,

		@Edate DATETIME

SET @Sdate=(SELECT MIN(Datetime) FROM dates)

SET @Edate=(SELECT MAX(Datetime) FROM dates)

WHILE @Sdate<=@Edate

BEGIN

	INSERT INTO @Temp20150717(DatetimeS)values (@Sdate)

	SET @Sdate=(SELECT MIN([Datetime]) FROM dates WHERE [Datetime]>=DATEADD(ss,5,@Sdate))

END



SELECT * FROM dates WHERE [Datetime] IN(SELECT DatetimeS from @Temp20150717)

结果如图;

Pact_Alice 2015-07-17

打赏
举报

;WITH cet AS ( SELECT 1 AS ID,'2015-05-05 08:00:00.000' AS shiftDate,0 AS Value UNION ALL SELECT 2 ,'2015-05-05 08:00:01.000',1 UNION ALL SELECT 3 ,'2015-05-05 08:00:02.000',2 UNION ALL SELECT 4 ,'2015-05-05 08:00:03.000',3 UNION ALL SELECT 5 ,'2015-05-05 08:00:04.000 ',4 UNION ALL SELECT 6 ,'2015-05-05 08:00:05.000',5 UNION ALL SELECT 7 ,'2015-05-05 08:00:06.000',6 UNION ALL SELECT 8 ,'2015-05-05 08:00:07.000',7 UNION ALL SELECT 9 ,'2015-05-05 08:00:08.000',8 UNION ALL SELECT 10 ,'2015-05-05 08:00:09.000 ',9 UNION ALL SELECT 11,'2015-05-05 08:00:10.000',10 UNION ALL SELECT 12 ,'2015-05-05 08:00:15.000',15 UNION ALL SELECT 13 ,'2015-05-05 08:00:19.000',19 UNION ALL SELECT 14 ,'2015-05-05 08:00:20.000',20 UNION ALL SELECT 15 ,'2015-05-05 08:00:30.000',30 UNION ALL SELECT 16 ,'2015-05-05 08:00:31.000',31 UNION ALL SELECT 17 ,'2015-05-05 08:00:36.000',36 ),cet1 AS ( SELECT *, ROW_NUMBER()OVER(PARTITION BY DATEDIFF(S,'2015-05-05 08:00:00.000',shiftDate)/5 ORDER BY cet.ID)AS N FROM cet ) SELECT * FROM cet1 WHERE N=1

reenjie 2015-07-17

打赏
举报


select Id,datetime,value from (
select *,
row_number() over(partition by DATEDIFF(SS,'2015-05-05 08:00:00.000',datetime)/5 order by datetime asc) as rId
from tablename as a)
) as b where rId=1

wangji666666 2015-07-17

打赏
举报

引用 1 楼 reenjie 的回复:

那就是以整個數據集的第一條數據為基礎，來查找符合條件的數據，對嗎？

是的

reenjie 2015-07-17

打赏
举报

那就是以整個數據集的第一條數據為基礎，來查找符合條件的數據，對嗎？

求SQL语句，取两条记录间隔5秒以上的数据集

相关推荐