每3分种一条数据，我只要取一个时间段内整点左右的一条数据怎么办？
如下数据
myid date data
1 2010-4-3 7:55:00 4
2 2010-4-3 7:55:00 3
1 2010-4-3 8:01:00 4
2 2010-4-3 8:01:00 3
1 2010-4-3 8:07:00 4
2 2010-4-3 8:07:00 3
1 2010-4-3 8:55:00 4
2 2010-4-3 8:55:00 3
1 2010-4-3 9:01:00 4
2 2010-4-3 9:01:00 3
1 2010-4-4 7:55:00 4
2 2010-4-4 7:55:00 3
1 2010-4-4 8:01:00 4
2 2010-4-4 8:01:00 2
1 2010-4-4 8:07:00 4
2 2010-4-4 8:07:00 2
1 2010-4-4 8:55:00 4
2 2010-4-4 8:55:00 3
1 2010-4-4 9:01:00 4
2 2010-4-4 9:01:00 2
如果得出以下结果呢
1 2010-4-3 8:01:00 4
2 2010-4-3 8:01:00 3
1 2010-4-3 9:01:00 4
2 2010-4-3 9:01:00 3
1 2010-4-4 8:01:00 4
2 2010-4-4 8:01:00 2
1 2010-4-4 9:01:00 4
2 2010-4-4 9:01:00 2
[解决办法]
也没个范围 怎么取?
[解决办法]
Take A Try...
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))>=0THEN 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 diffsecondsFROM tbGROUP BY intdate)SELECT * FROM T b WHERE NOT EXISTS(SELECT 1 FROM T WHERE diffseconds<b.diffseconds)
[解决办法]
有bug
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))>=0THEN 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 diffsecondsFROM tbGROUP BY intdate)SELECT * FROM T b WHERE NOT EXISTS(SELECT 1 FROM T WHERE diffseconds<b.diffseconds)
[解决办法]
------------------------------------------------ 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]GOCREATE TABLE [tb] ([myid] [int],[date] [datetime],[data] [int])INSERT INTO [tb]SELECT '1','2010-4-3 7:55:00','4' UNION ALLSELECT '2','2010-4-3 7:55:00','3' UNION ALLSELECT '1','2010-4-3 8:01:00','4' UNION ALLSELECT '2','2010-4-3 8:01:00','3' UNION ALLSELECT '1','2010-4-3 8:07:00','4' UNION ALLSELECT '2','2010-4-3 8:07:00','3' UNION ALLSELECT '1','2010-4-3 8:55:00','4' UNION ALLSELECT '2','2010-4-3 8:55:00','3' UNION ALLSELECT '1','2010-4-3 9:01:00','4' UNION ALLSELECT '2','2010-4-3 9:01:00','3' UNION ALLSELECT '1','2010-4-4 7:55:00','4' UNION ALLSELECT '2','2010-4-4 7:55:00','3' UNION ALLSELECT '1','2010-4-4 8:01:00','4' UNION ALLSELECT '2','2010-4-4 8:01:00','2' UNION ALLSELECT '1','2010-4-4 8:07:00','4' UNION ALLSELECT '2','2010-4-4 8:07:00','2' UNION ALLSELECT '1','2010-4-4 8:55:00','4' UNION ALLSELECT '2','2010-4-4 8:55:00','3' UNION ALLSELECT '1','2010-4-4 9:01:00','4' UNION ALLSELECT '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 42 2010-04-03 08:01:00.000 31 2010-04-03 09:01:00.000 42 2010-04-03 09:01:00.000 31 2010-04-04 08:01:00.000 42 2010-04-04 08:01:00.000 21 2010-04-04 09:01:00.000 42 2010-04-04 09:01:00.000 2(8 行受影响)*/
[解决办法]
顶一个睡觉 ，虎虎
[解决办法]