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

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

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

每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

相关资料更多>

  1. 新手有关问题，给讲一下哈
  2. 请问SQL存储过程读取另一个表的字段值
  3. 是sql server 2000的BUG仍是.
  4. 数据存储有关问题
  5. 查询两个表的有关问题
  6. 数据字典能干嘛？ 有范例加分！ 上个帖子回的人好少！ 气愤！ 难道是50分太少？
  7. 一段小弟我看不明白的SQL语句，请大家帮个忙
  8. MS SQL 2008 用SSIS导出数据文件，怎么设定不要列名
  9. 列转行/计算,该怎么处理