每3分种一条数据，小弟我只要取一个时间段内整点左右的一条数据怎么处理-MSSQL教程-爱易网页

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

日期：2014-05-18　浏览次数：20521 次

每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...

SQL code


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 t

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

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

相关资料更多>

推荐阅读更多>