请教,用一条SQL命令完成指定时间段内指定间隔的数据 select [time] from tab where [time] between timeStart and timeEnd and datediff([间隔时间的单位(年/月/日/时/分/秒)],timeStart,[time])%timeSpan=0datediff()函数用法参考 http://msdn.microsoft.com/zh-cn/library/ms189794(v=sql.105).aspx 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select max(time) from tab where time < timeSearch;上一条就是这样的上面的方法循环太多,想改成一条SQL命令 %timeSpan 不行,因为需要补数据.原始库中的数据2013-08-01 09:44:30|1|1.02013-08-01 09:45:24|1|1.02013-08-01 09:45:28|1|1.02013-08-01 09:45:31|1|1.02013-08-01 09:45:33|1|1.02013-08-01 09:45:36|1|1.02013-08-01 09:45:00 按10分钟间隔查询2013-08-01 09:45:00|2013-08-01 09:44:30|1|1.02013-08-01 09:55:00|2013-08-01 09:45:36|1|1.02013-08-01 10:05:00|2013-08-01 09:45:36|1|1.02013-08-01 10:15:00|2013-08-01 09:45:36|1|1.02013-08-01 10:25:00|2013-08-01 09:45:36|1|1.02013-08-01 10:35:00|2013-08-01 09:45:36|1|1.0不知道这样能不能说清楚了. CREATE TABLE #temp([time] DATETIME, field1 INT, field2 FLOAT)INSERT #tempselect '2013-08-01 09:44:30','1','1.0'union allselect '2013-08-01 09:45:24','1','1.0'union allselect '2013-08-01 09:45:28','1','1.0'union allselect '2013-08-01 09:45:31','1','1.0'union allselect '2013-08-01 09:45:33','1','1.0'union allselect '2013-08-01 09:45:36','1','1.0'DECLARE @timeStart DATETIME, @timeEnd DATETIMESELECT @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) ACROSS APPLY( SELECT TOP(1) * FROM #temp M WHERE M.[time] < A.timePoint ORDER BY M.[time] DESC) B/*timePoint time field1 field22013-08-01 09:45:00.000 2013-08-01 09:44:30.000 1 12013-08-01 09:55:00.000 2013-08-01 09:45:36.000 1 12013-08-01 10:05:00.000 2013-08-01 09:45:36.000 1 12013-08-01 10:15:00.000 2013-08-01 09:45:36.000 1 12013-08-01 10:25:00.000 2013-08-01 09:45:36.000 1 12013-08-01 10:35:00.000 2013-08-01 09:45:36.000 1 1*/ 我用的是sqlite 上面的指令识别不了 朋友帮我搞定了,用的是left join... 楼主,能把你实现的sql分享一下不,谢谢 多like查询 如何优化这条更新语句 如何创建以下视图? SQL 置疑\紧急模式 大日志的数据库压缩 求Sql语句 access是算方差的函数和sql server中怎么算出来结果不一样啊?很急,在线等//bow不够分可以再加。 如此SQL,怎么取值?? 关于全文索引 请问有没有转化字符串为数字的函数!!!!!!!!! 2表相连出现重复数据。 sql server关于DateTime格式检索、别名的问题
select max(time) from tab where time < timeSearch;
上一条就是这样的上面的方法循环太多,想改成一条SQL命令
%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.02013-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
不知道这样能不能说清楚了.
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
*/
用的是left join...