每3分种一条数据，小弟我只要取一个时间段内整点左右的一条数据怎么处理

WITH T AS
(SELECT *,CONVERT(DATETIME,STUFF(CONVERT(VARCHAR(19),getdate(),120),15,5,'00:00'),120) AS intdate,
CASE 
WHEN DATEDIFF(SECOND,GETDATE(),CONVERT(DATETIME,STUFF(CONVERT(VARCHAR(19),date,120),15,5,'00:00'),120))>=0
THEN DATEDIFF(SECOND,GETDATE(),CONVERT(DATETIME,STUFF(CONVERT(VARCHAR(19),date,120),15,5,'00:00'),120))
ELSE 0-DATEDIFF(SECOND,GETDATE(),CONVERT(DATETIME,STUFF(CONVERT(VARCHAR(19),date,120),15,5,'00:00'),120))
END AS diffseconds
FROM tb
GROUP BY intdate)
SELECT * FROM T b WHERE NOT EXISTS(SELECT 1 FROM T WHERE diffseconds<b.diffseconds)

------解决方案--------------------
有bug

SQL code

WITH T AS
(SELECT *,CONVERT(DATETIME,STUFF(CONVERT(VARCHAR(19),date,120),15,5,'00:00'),120) AS intdate,
CASE 
WHEN DATEDIFF(SECOND,GETDATE(),CONVERT(DATETIME,STUFF(CONVERT(VARCHAR(19),date,120),15,5,'00:00'),120))>=0
THEN DATEDIFF(SECOND,GETDATE(),CONVERT(DATETIME,STUFF(CONVERT(VARCHAR(19),date,120),15,5,'00:00'),120))
ELSE 0-DATEDIFF(SECOND,GETDATE(),CONVERT(DATETIME,STUFF(CONVERT(VARCHAR(19),date,120),15,5,'00:00'),120))
END AS diffseconds
FROM tb
GROUP BY intdate)
SELECT * FROM T b WHERE NOT EXISTS(SELECT 1 FROM T WHERE diffseconds<b.diffseconds)

------解决方案--------------------

SQL code

----------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2010-05-10 00:26:24
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog   : http://blog.csdn.net/htl258
----------------------------------------------

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb] ([myid] [int],[date] [datetime],[data] [int])
INSERT INTO [tb]
SELECT '1','2010-4-3 7:55:00','4' UNION ALL
SELECT '2','2010-4-3 7:55:00','3' UNION ALL
SELECT '1','2010-4-3 8:01:00','4' UNION ALL
SELECT '2','2010-4-3 8:01:00','3' UNION ALL
SELECT '1','2010-4-3 8:07:00','4' UNION ALL
SELECT '2','2010-4-3 8:07:00','3' UNION ALL
SELECT '1','2010-4-3 8:55:00','4' UNION ALL
SELECT '2','2010-4-3 8:55:00','3' UNION ALL
SELECT '1','2010-4-3 9:01:00','4' UNION ALL
SELECT '2','2010-4-3 9:01:00','3' UNION ALL
SELECT '1','2010-4-4 7:55:00','4' UNION ALL
SELECT '2','2010-4-4 7:55:00','3' UNION ALL
SELECT '1','2010-4-4 8:01:00','4' UNION ALL
SELECT '2','2010-4-4 8:01:00','2' UNION ALL
SELECT '1','2010-4-4 8:07:00','4' UNION ALL
SELECT '2','2010-4-4 8:07:00','2' UNION ALL
SELECT '1','2010-4-4 8:55:00','4' UNION ALL
SELECT '2','2010-4-4 8:55:00','3' UNION ALL
SELECT '1','2010-4-4 9:01:00','4' UNION ALL
SELECT '2','2010-4-4 9:01:00','2'

--SELECT * FROM [tb]

-->SQL查询如下:
;with t as
(
    select top (select count(distinct convert(char,date,23))*24 from tb)
        date= dateadd(hh,row_number()over(partition by a.date ORDER by id)-1,a.date+' 00:00:00')
    from sysobjects,(select distinct convert(varchar,date,23) as date from tb) a
)
,t1 as
(
    select a.*,b.date date2 
    from tb a
        outer apply(
            select * from t 
            where a.date between dateadd(mi,-3,date) and dateadd(mi,3,date) --这里与整点上下相差3分钟，可以自己根据需要进行调整
            ) b  
)
select myid,date,data 
from t1 
where date2 is not null
/*
myid        date                    data
----------- ----------------------- -----------
1           2010-04-03 08:01:00.000 4
2           2010-04-03 08:01:00.000 3
1           2010-04-03 09:01:00.000 4
2           2010-04-03 09:01:00.000 3
1           2010-04-04 08:01:00.000 4
2           2010-04-04 08:01:00.000 2
1           2010-04-04 09:01:00.000 4
2           2010-04-04 09:01:00.000 2

(8 行受影响)
*/

------解决方案--------------------
顶一个睡觉 ，虎虎

------解决方案--------------------

探讨

SQL code
--上面忽略了点东西，修正一下，以此代码为准：

----------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-05-10 00:26:24
-- Version: Microsof……

------解决方案--------------------
为何7点的没取数？？啥逻辑
有偿支持