水位历史表中每隔10分钟一条记录,如
表:tblLevel
gettime value
2007-5-17 0:0:10 257.10
2007-5-17 0:10:07 257.12
...
2007-5-17 12:00:05 284.13
2007-5-17 12:10:08 283.7
...
如何得到日报,(整点,最近值)结果如下:
2007-5-17 0:0:0 257.10
...
2007-5-17 12:00:00 284.13
...
表:tblLevel
gettime value
2007-5-17 0:0:10 257.10
2007-5-17 0:10:07 257.12
...
2007-5-17 12:00:05 284.13
2007-5-17 12:10:08 283.7
...
如何得到日报,(整点,最近值)结果如下:
2007-5-17 0:0:0 257.10
...
2007-5-17 12:00:00 284.13
...
insert @a select '2007-5-17 0:0:10', 257.10
union all select '2007-5-17 0:10:07', 257.12
union all select '2007-5-17 12:00:05', 284.13
union all select '2007-5-17 12:10:08', 283.7select a.* from @a a
Inner Join
(select convert(varchar(13),gettime,113) x,min(gettime) gettime from @a group by convert(varchar(13),gettime,113)) b
ON
a.gettime=b.gettime
RETURNS decimal(12,2)
AS
BEGIN
declare @return decimal(12,2)
select @return=value from a where DATEDIFF(s,gettime,@datetime)=
(select max(DATEDIFF(s,gettime,@datetime)) from a where DATEDIFF(s,gettime,@datetime)<0)
Return @return
ENDselect dbo.TimeCalc('2007-05-17 00:00:00')
select dbo.TimeCalc('2007-05-17 12:00:00')
输入整点日期返回符合的值
判断的标准有2种,一种是比整点大,一种是比整点小,我这里根据楼主意思用整点大的,用max