取出数据库里最靠近指定时间点的数据

Question

本帖最后由 hoodlq 于 2014-09-02 09:13:28 编辑

Answer 1

貌似有点复杂，如下是一个思路
IF OBJECT_ID('#test') IS NOT NULL
DROP TABLE #test
GOCREATE TABLE #test
(
  Date  DATETIME,
  DATA  DECIMAL(10,1)
)INSERT INTO #test( Date, DATA ) VALUES('2014-09-01 07:57:00',22.2)
INSERT INTO #test( Date, DATA ) VALUES('2014-09-01 07:58:00',22.5)
INSERT INTO #test( Date, DATA ) VALUES('2014-09-01 08:05:00',23.2)
INSERT INTO #test( Date, DATA ) VALUES('2014-09-01 08:04:00',23.1)
INSERT INTO #test( Date, DATA ) VALUES('2014-09-01 16:39:00',21.2)
INSERT INTO #test( Date, DATA ) VALUES('2014-09-01 17:10:00',20.0)DECLARE @max7 DATETIME
DECLARE @min8 DATETIME
DECLARE @standard DATETIME
 
--取整8点记录       
SELECT TOP 1 Date FROM #test WHERE CONVERT(VARCHAR(16),Date,20) =CONVERT(VARCHAR(13),'2014-09-01 08:00:00',20) --取最大7点记录
SELECT @max7 = MAX(Date)  FROM 
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY Date,DATA ORDER BY DATe) 'ID'   FROM #test WHERE CONVERT(VARCHAR(16),Date,20) < CONVERT(VARCHAR(13),'2014-09-01 08:00:00',20) 
) t GROUP BY ID--取最小8点记录
SELECT @min8 = MIN(Date) FROM 
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY Date,DATA ORDER BY DATe) 'ID'   FROM #test WHERE CONVERT(VARCHAR(16),Date,20) > CONVERT(VARCHAR(13),'2014-09-01 08:00:00',20) 
) t GROUP BY ID--获取@max7和@min8中最接近8点的标准时间   min( | {@max7,@min8} - 8 |)
IF (@min8 - CONVERT(VARCHAR(32),'2014-09-01 08:00:00',20)) < (CONVERT(VARCHAR(32),'2014-09-01 08:00:00',20) - @max7)
SET @standard = @min8
ELSE
SET @standard = @max7SELECT @max7 'max7',@min8 'min8',@standard 'standard'

Answer 2

with cte as 
(select * ,ABS(DATEDIFF(ss,am8,mydate)) as mydateam8,
ABS(DATEDIFF(ss,pm5,mydate)) as mydatepm5 from (select mydate,data,dateadd(HH,8,(dateadd(day,DATEDIFF(DAY,'20140901',mydate),'20140901'))) as am8,
dateadd(HH,17,(dateadd(day,DATEDIFF(DAY,'20140901',mydate),'20140901'))) as pm5
 from #tt) as p)
--算出各行对2个时间点的时间间隔。
 select mydate,data from cte 
 where mydateam8=(select MIN(mydateam8) from cte)
 or mydatepm5=(select MIN(mydatepm5) from cte)

Answer 3

用一个临时表存储生成这个月的每天的日期、每天8点和17点的时间，然后用你的表和临时表关联，用datediff函数取你的表的时间和每天的8点和17点的时间的秒数，最小秒即为靠的最近的

Answer 4

从给定的测试数据看，精确度为分（Minute）datediff函数似乎需要一个时间参考点。如果给定一个模糊的时间查找是比较麻烦的事情。实际上只要取三条数据（如果有的话）就行，以8点为例：
取整8点记录（如果有）1条
取最大7点记录1条（即早于8点且离8点最近的一条）
取最小8记录1条（即晚于8点且离8点最近的一条）

Answer 5

WITH 表1(Date,data) AS (
    SELECT Convert(datetime,'2014-09-01 07:58:00',120),22.5 UNION ALL
    SELECT '2014-09-01 08:05:00',23.2 UNION ALL
    SELECT '2014-09-01 16:39:00',21.2 UNION ALL
    SELECT '2014-09-01 17:10:00',20.0
),
-- 分离日期、时间
t (Date,data,d,t) AS (
SELECT *,
       Convert(varchar(10),Date,120),
       Convert(varchar(10),Date,114)
  FROM 表1
 WHERE Date >= '2014-09-01'
   AND Date <  '2014-10-01'
),
-- 每天按离 08:00 的远近排序
t1 (Date,data,d,n) AS (
SELECT Date,data,d,
       ROW_NUMBER() OVER (PARTITION BY d
                              ORDER BY ABS(datediff(second,t,'08:00:00'))
                         )
  FROM t
),
-- 每天按离 17:00 的远近排序
t2 (Date,data,d,n) AS (
SELECT Date,data,d,
       ROW_NUMBER() OVER (PARTITION BY d
                              ORDER BY ABS(datediff(second,t,'17:00:00'))
                         )
  FROM t
)
SELECT Date,data
  FROM (
        SELECT * FROM T1 WHERE n=1
        UNION ALL
        SELECT * FROM T2 WHERE n=1
       ) a
 ORDER BY Date
Date                                                       data
----------------------- ---------------------------------------
2014-09-01 07:58:00.000                                    22.5
2014-09-01 17:10:00.000                                    20.0