背景:测试数据采集是否按照采集周期进行数据采集。
采集周期:10s。
【表结构】
CREATE TABLE [dbo].[TDATA] (
[DDATE] [datetime] NOT NULL,
[DVALUE] [float] NULL )
【数据】
2017-02-27 11:50:30.000 3479
2017-02-27 11:50:40.000 3481
2017-02-27 11:50:42.000 3483
2017-02-27 11:50:53.000 3485
【SQL语句 - 查询最大的采集存库间隔】
原理:第二行-第一行,第三行-第二行......
实现:
WITH newtbl AS
(
SELECT ROW_NUMBER() OVER(ORDER BY ddate) AS id, * FROM dbo.TDATA
)
select max(diff) from (
SELECT *,ISNULL(
(SELECT datediff(second, ddate, t.ddate) FROM newtbl WHERE id=t.id-1 AND ddate<t.ddate ),
0) AS diff FROM newtbl t
) as tbllast