例如:current | time
1.2 | 2011-3-23 10:30:12
1.3 | 2011-3-23 10:30:49
1.34 | 2011-3-23 10:31:04
1.4 | 2011-3-23 10:31:30
...
1.5 | 2011-3-23 10:40:24
1.4 | 2011-3-23 10:41:14
1.5 | 2011-3-23 10:42:02求SQL 查出每隔10分钟内的第一条数据如下结果
current | time
1.2 | 2011-3-23 10:30:12
1.5 | 2011-3-23 10:40:24相隔10分钟时从第一条数据算起 就是第一个10分钟就是 2011-3-23 10:30:12 到 2011-3-23 10:40:12
1.2 | 2011-3-23 10:30:12
1.3 | 2011-3-23 10:30:49
1.34 | 2011-3-23 10:31:04
1.4 | 2011-3-23 10:31:30
...
1.5 | 2011-3-23 10:40:24
1.4 | 2011-3-23 10:41:14
1.5 | 2011-3-23 10:42:02求SQL 查出每隔10分钟内的第一条数据如下结果
current | time
1.2 | 2011-3-23 10:30:12
1.5 | 2011-3-23 10:40:24相隔10分钟时从第一条数据算起 就是第一个10分钟就是 2011-3-23 10:30:12 到 2011-3-23 10:40:12
解决方案 »
- 关于字段自动进位,保留六位的情况
- 求幂Arithmetic overflow error
- T-sql语句
- 请问select语句中能不能用变量名(关于存储过程的)?
- 两个查询结果,一个结果中包含另一结果,怎么把包含的结果去掉
- 使用Provider=Microsoft.Jet.OLEDB.4.0 这个,需要ADO还是DAO的安装引擎?
- 请教一个中位数计算问题
- 求教,一个语句的写法
- 如何通过INTERNET连接到远地的SQL SERVER
- 求助批量导入数据时,数据重复问题的解决方案
- SQL2005修改数据库记录时不能添加中文?
- 请教:delete from Table1 where SN not in (select distinct SN from Table2)这个效率怎么样?
from tb
group by convert(varchar(15),time,120)
declare @t table (c float ,time datetime)insert into @t
select 1.2,'2011-3-23 10:30:12' union all
select 1.3,'2011-3-23 10:30:49' union all
select 1.34,'2011-3-23 10:31:04' union all
select 1.4,'2011-3-23 10:31:30' union all
select 1.5,'2011-3-23 10:40:24' union all
select 1.4,'2011-3-23 10:41:14' union all
select 1.5,'2011-3-23 10:42:02' declare @c table (st datetime,et datetime)
insert into @c
select '2011-3-23 10:30:00','2011-3-23 10:40:00' union all
select '2011-3-23 10:40:00','2011-3-23 10:50:00'
select a.st ,MIN(b.time),
replace(min(CONVERT(nchar(20),b.time,120) + CONVERT(nchar(10),b.c)),CONVERT(nchar(20),min(b.time),120),'')
from @c a left join @t b on b.time >= a.st and b.time <a.et
group by a.st ------------------2011-03-23 10:30:00.000 2011-03-23 10:30:12.000 1.2
2011-03-23 10:40:00.000 2011-03-23 10:40:24.000 1.5
if object_id('[TB]') is not null drop table [TB]
create table [TB]([current] varchar(4),[time] datetime)
insert [TB]
select '1.2','2011-3-23 10:30:12' union all
select '1.3','2011-3-23 10:30:49' union all
select '1.34','2011-3-23 10:31:04' union all
select '1.4','2011-3-23 10:31:30' union all
select '1.5','2011-3-23 10:40:24' union all
select '1.4','2011-3-23 10:41:14' union all
select '1.5','2011-3-23 10:42:02'DECLARE @TT SMALLDATETIME
SET @TT = '2011-03-23 10:30:12.000'SELECT [time] FROM tb WHERE DATEDIFF(mi,@TT,CONVERT(SMALLDATETIME,[time])) = 10
--2011-03-23 10:40:24.000
declare @t table (c float ,time datetime)insert into @t
select 1.2,'2011-3-23 10:30:12' union all
select 1.3,'2011-3-23 10:30:49' union all
select 1.34,'2011-3-23 10:31:04' union all
select 1.4,'2011-3-23 10:31:30' union all
select 1.5,'2011-3-23 10:40:24' union all
select 1.4,'2011-3-23 10:41:14' union all
select 1.5,'2011-3-23 10:42:02'
select convert(varchar(15),time,120),min(time),
replace(min(CONVERT(nchar(20),time,120) + CONVERT(nchar(10),c)),CONVERT(nchar(20),min(time),120),'')
from @t
group by convert(varchar(15),time,120)
-----------------------
2011-03-23 10:3 2011-03-23 10:30:12.000 1.2
2011-03-23 10:4 2011-03-23 10:40:24.000 1.5
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-23 11:05:55
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([current] numeric(3,2),[time] datetime)
insert [tb]
select 1.2,'2011-3-23 10:30:12' union all
select 1.3,'2011-3-23 10:30:49' union all
select 1.34,'2011-3-23 10:31:04' union all
select 1.4,'2011-3-23 10:31:30' union all
select 1.5,'2011-3-23 10:40:24' union all
select 1.4,'2011-3-23 10:41:14' union all
select 1.5,'2011-3-23 10:42:02'
--------------开始查询--------------------------
select
*
from
(select top 1* from tb) a
cross apply
(select top 1 * from tb b where DATEDIFF(mi,a.time,b.time)>10)b
----------------结果----------------------------
/* current time current time
--------------------------------------- ----------------------- --------------------------------------- -----------------------
1.20 2011-03-23 10:30:12.000 1.40 2011-03-23 10:41:14.000(1 行受影响)*/