请教,用一条SQL命令完成指定时间段内指定间隔的数据

[HuWenjin]

查询指定时间段内的数据,按指定的间隔排序,如果不存就在补上一条

数据表中有个 time 字段
如果用多条SQL命令来作,是这样的

timeStart 起始时间
timeEnd 结束时间
timeSpan 间隔时间

timeSearch = timeStart;

where( timeSearch < timeEnd )
{
select max(time) from tab where time < timeSearch;
timeSearch += timeSpan;
}

[Shawn]

不存就在补上一条？是什么意思，楼主给个数据吧

[唐诗三百首]

select [time]
 from tab 
 where [time] between timeStart and timeEnd
 and datediff([间隔时间的单位(年/月/日/时/分/秒)],timeStart,[time])%timeSpan=0

datediff()函数用法参考 http://msdn.microsoft.com/zh-cn/library/ms189794(v=sql.105).aspx

[HuWenjin]

引用 1 楼 wwwwgou 的回复:

不存就在补上一条？是什么意思，楼主给个数据吧

select max(time) from tab where time < timeSearch; 上一条就是这样的 上面的方法循环太多,想改成一条SQL命令

[HuWenjin]

引用 2 楼 ap0405140 的回复:

select [time]
 from tab 
 where [time] between timeStart and timeEnd
 and datediff([间隔时间的单位(年/月/日/时/分/秒)],timeStart,[time])%timeSpan=0

datediff()函数用法参考 http://msdn.microsoft.com/zh-cn/library/ms189794(v=sql.105).aspx

%timeSpan 不行,因为需要补数据. 原始库中的数据 2013-08-01 09:44:30|1|1.0 2013-08-01 09:45:24|1|1.0 2013-08-01 09:45:28|1|1.0 2013-08-01 09:45:31|1|1.0 2013-08-01 09:45:33|1|1.0 2013-08-01 09:45:36|1|1.0 2013-08-01 09:45:00 按10分钟间隔查询 2013-08-01 09:45:00|2013-08-01 09:44:30|1|1.0 2013-08-01 09:55:00|2013-08-01 09:45:36|1|1.0 2013-08-01 10:05:00|2013-08-01 09:45:36|1|1.0 2013-08-01 10:15:00|2013-08-01 09:45:36|1|1.0 2013-08-01 10:25:00|2013-08-01 09:45:36|1|1.0 2013-08-01 10:35:00|2013-08-01 09:45:36|1|1.0 不知道这样能不能说清楚了.

[Shawn]

CREATE TABLE #temp([time] DATETIME, field1 INT, field2 FLOAT)
INSERT #temp
select '2013-08-01 09:44:30','1','1.0'union all
select '2013-08-01 09:45:24','1','1.0'union all
select '2013-08-01 09:45:28','1','1.0'union all
select '2013-08-01 09:45:31','1','1.0'union all
select '2013-08-01 09:45:33','1','1.0'union all
select '2013-08-01 09:45:36','1','1.0'

DECLARE @timeStart DATETIME, @timeEnd DATETIME
SELECT @timeStart = '2013-08-01 09:45:00', @timeEnd = '2013-08-01 10:35:00'

SELECT A.timePoint, B.* FROM
(
	SELECT
		timePoint = DATEADD(minute, 10*number, @timeStart)
	FROM master..spt_values b
	WHERE type = 'p'
		AND DATEADD(minute, 10*number, @timeStart) <= @timeEnd
) A
CROSS APPLY
(
	SELECT TOP(1) *
	FROM #temp M
	WHERE M.[time] < A.timePoint
	ORDER BY M.[time] DESC
) B
/*
timePoint	time	field1	field2
2013-08-01 09:45:00.000	2013-08-01 09:44:30.000	1	1
2013-08-01 09:55:00.000	2013-08-01 09:45:36.000	1	1
2013-08-01 10:05:00.000	2013-08-01 09:45:36.000	1	1
2013-08-01 10:15:00.000	2013-08-01 09:45:36.000	1	1
2013-08-01 10:25:00.000	2013-08-01 09:45:36.000	1	1
2013-08-01 10:35:00.000	2013-08-01 09:45:36.000	1	1
*/

[HuWenjin]

朋友帮我搞定了, 用的是left join...

[HuWenjin]

引用 5 楼 wwwwgou 的回复:

CREATE TABLE #temp([time] DATETIME, field1 INT, field2 FLOAT)
INSERT #temp
select '2013-08-01 09:44:30','1','1.0'union all
select '2013-08-01 09:45:24','1','1.0'union all
select '2013-08-01 09:45:28','1','1.0'union all
select '2013-08-01 09:45:31','1','1.0'union all
select '2013-08-01 09:45:33','1','1.0'union all
select '2013-08-01 09:45:36','1','1.0'

DECLARE @timeStart DATETIME, @timeEnd DATETIME
SELECT @timeStart = '2013-08-01 09:45:00', @timeEnd = '2013-08-01 10:35:00'

SELECT A.timePoint, B.* FROM
(
	SELECT
		timePoint = DATEADD(minute, 10*number, @timeStart)
	FROM master..spt_values b
	WHERE type = 'p'
		AND DATEADD(minute, 10*number, @timeStart) <= @timeEnd
) A
CROSS APPLY
(
	SELECT TOP(1) *
	FROM #temp M
	WHERE M.[time] < A.timePoint
	ORDER BY M.[time] DESC
) B
/*
timePoint	time	field1	field2
2013-08-01 09:45:00.000	2013-08-01 09:44:30.000	1	1
2013-08-01 09:55:00.000	2013-08-01 09:45:36.000	1	1
2013-08-01 10:05:00.000	2013-08-01 09:45:36.000	1	1
2013-08-01 10:15:00.000	2013-08-01 09:45:36.000	1	1
2013-08-01 10:25:00.000	2013-08-01 09:45:36.000	1	1
2013-08-01 10:35:00.000	2013-08-01 09:45:36.000	1	1
*/

我用的是sqlite 上面的指令识别不了

[pingchangxin76]

楼主，能把你实现的sql分享一下不，谢谢