SQL统计记录数,一个时间段内的记录只算一次
要查询所有的记录,但是每15分钟的记录只算一次
也就是以最小时间为基数,除去15分钟内的其它记录,再以第一个大于15分钟的时间为第二基数,再除去15分钟内的其它记录,依次类推比如有记录如下:
1 2011-12-14 10:00:01 (基础时间)
2 2011-12-14 10:12:01 (跟1比,小于15分钟,去除)
3 2011-12-14 10:16:01 (跟1比,大小15分钟,保留,做第二基础时间)
4 2011-12-14 10:33:02 (跟3比,大小15分钟,保留,做第三基础时间)
.
.
.
依次下去
统计出来的应该是3条,即1,3,4
SQL语句该如何处理?
不会只有循环这个方法吧?
不知道表达得清不清楚。
要查询所有的记录,但是每15分钟的记录只算一次
也就是以最小时间为基数,除去15分钟内的其它记录,再以第一个大于15分钟的时间为第二基数,再除去15分钟内的其它记录,依次类推比如有记录如下:
1 2011-12-14 10:00:01 (基础时间)
2 2011-12-14 10:12:01 (跟1比,小于15分钟,去除)
3 2011-12-14 10:16:01 (跟1比,大小15分钟,保留,做第二基础时间)
4 2011-12-14 10:33:02 (跟3比,大小15分钟,保留,做第三基础时间)
.
.
.
依次下去
统计出来的应该是3条,即1,3,4
SQL语句该如何处理?
不会只有循环这个方法吧?
不知道表达得清不清楚。
select top 1 * from tb order by id
union all
select
*
from
tb t
where
exists(select 1 from tb where convert(varchar(10),time,120)=convert(varchar(10),time,120) and datediff(mi,time,t.time)>15)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-12-16 10:23:53
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[time] datetime)
insert [tb]
select 1,'2011-12-14 10:00:01' union all
select 2,'2011-12-14 10:12:01' union all
select 3,'2011-12-14 10:16:01' union all
select 4,'2011-12-14 10:33:02'
--------------开始查询--------------------------
select top 1 * from tb
union all
select
*
from
tb t
where
exists(select 1 from tb where convert(varchar(10),time,120)=convert(varchar(10),time,120) and datediff(mi,time,t.time)>15)----------------结果----------------------------
/* id time
----------- -----------------------
1 2011-12-14 10:00:01.000
3 2011-12-14 10:16:01.000
4 2011-12-14 10:33:02.000(3 行受影响)*/
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([Col1] int,[Col2] Datetime)
Insert #T
select 1,'2011-12-14 10:00:01' union all
select 2,'2011-12-14 10:12:01' union all
select 3,'2011-12-14 10:16:01' union all
select 4,'2011-12-14 10:33:02'
Go
SELECT [Col1],[Col2]
FROM (Select
ROW_NUMBER()OVER(PARTITION BY DATEDIFF(mi,'2011-12-14 10:00:00',[Col2])/15 ORDER BY [Col1]) AS row,*
from #T
)t
WHERE row=1
/*
Col1 Col2
1 2011-12-14 10:00:01.000
3 2011-12-14 10:16:01.000
4 2011-12-14 10:33:02.000
*/
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([Col1] int,[Col2] Datetime)
Insert #T
select 1,'2011-12-14 10:00:01' union all
select 2,'2011-12-14 10:12:01' union all
select 3,'2011-12-14 10:16:01' union all
select 4,'2011-12-14 10:33:02'
Go
DECLARE @dt1 DATETIME,@dt2 DATETIME
SET @dt1='2011-12-14 10:00:01'--開始
SET @dt2='2011-12-16' --結束SELECT [Col1],[Col2]
FROM (Select
ROW_NUMBER()OVER(PARTITION BY DATEDIFF(mi,@dt1,[Col2])/15 ORDER BY [Col1]) AS row,*
from #T
WHERE [Col2]<=@dt2
)t
WHERE row=1
/*
Col1 Col2
1 2011-12-14 10:00:01.000
3 2011-12-14 10:16:01.000
4 2011-12-14 10:33:02.000
*/
insert into tb select 1,'2011-12-14 10:00:01'
insert into tb select 2,'2011-12-14 10:12:01'
insert into tb select 3,'2011-12-14 10:16:01'
insert into tb select 4,'2011-12-14 10:33:02'
go
select count(distinct CONVERT(varchar(14),dt,120)+ltrim(DATEPART(mi,dt)/15)) from tb
/*-----------
3(1 行受影响)*/
go
drop table tb
insert into tb select 1,'2011-12-14 10:00:01'
insert into tb select 2,'2011-12-14 10:12:01'
insert into tb select 3,'2011-12-14 10:16:01'
insert into tb select 4,'2011-12-14 10:33:02'
insert into tb select 5,'2011-12-14 10:34:01'
insert into tb select 6,'2011-12-14 11:18:20'
go
select count(distinct CONVERT(varchar(14),dt,120)+ltrim(DATEPART(mi,dt)/15)) from tb
/*-----------
4(1 行受影响)*/
go
drop table tb
insert into tb select 1,'2011-12-14 10:00:01'
insert into tb select 2,'2011-12-14 10:12:01'
insert into tb select 3,'2011-12-14 10:16:01'
insert into tb select 4,'2011-12-14 10:33:02'
insert into tb select 5,'2011-12-14 10:34:01'
insert into tb select 6,'2011-12-14 11:18:20'
insert into tb select 7,'2011-11-10 01:01:20'
go
select count(distinct CONVERT(varchar(14),dt,120)+ltrim(DATEPART(mi,dt)/15)) from tb
/*-----------
5(1 行受影响)*/
go
drop table tb
2011-12-14 10:02:00--不是一定要0分為基數時,必髯要用datediff計算
insert into tb values(1,'2011-12-14 10:00:01')
insert into tb values(2,'2011-12-14 10:12:01')
insert into tb values(3,'2011-12-14 10:16:01')
insert into tb values(4,'2011-12-14 10:33:02')
create table tb2(id int , dt datetime)
godeclare @dt datetime
declare @dt2 datetime
set @dt2 = null
declare cur cursor fast_forward for
select dt from tb order by dt;
open cur;
fetch next from cur into @dt;
while @@fetch_status=0
begin
if @dt2 is null
begin
insert into tb2 select * from tb where dt = @dt
set @dt2 = @dt
end
else
if datediff(ss , @dt2 , @dt) >= 60 * 15
begin
insert into tb2 select * from tb where dt = @dt
set @dt2 = @dt
end
fetch next from cur into @dt;
end
close cur;
deallocate cur;select * From tb2drop table tb,tb2/*
id dt
----------- ------------------------------------------------------
1 2011-12-14 10:00:01.000
3 2011-12-14 10:16:01.000
4 2011-12-14 10:33:02.000(所影响的行数为 3 行)*/
insert into tb
select 1,'2011-12-14 10:00:01' union
select 2,'2011-12-14 10:12:01' union
select 3,'2011-12-14 10:16:01' union
select 4,'2011-12-14 10:33:02' union
select 5,'2011-12-14 10:55:02' union
select 6,'2011-12-15 10:33:02' union
select 7,'2011-12-15 10:34:02' union
select 8,'2011-12-15 11:34:02' select *,null as into #tb from tb
declare @dt1 datetime=(select MIN(dt) from tb),@dt2 datetime
update #tb set =case when DATEDIFF(MI,@dt1,dt)>15 then 1 end,
@dt1=case when DATEDIFF(mi,@dt1,@dt2)>15 then @dt2 else @dt1 end,@dt2=dt
select top 1 id,dt from #tb
union all
select id,dt from #tb where =1
drop table tb,#tb /*
id dt
----------- -----------------------
1 2011-12-14 10:00:01.000
3 2011-12-14 10:16:01.000
4 2011-12-14 10:33:02.000
5 2011-12-14 10:55:02.000
6 2011-12-15 10:33:02.000
8 2011-12-15 11:34:02.000(6 行受影响)
(id int, udate datetime)insert into inghot
select 1, '2011-12-14 10:00:01' union all
select 2, '2011-12-14 10:12:01' union all
select 3, '2011-12-14 10:16:01' union all
select 4, '2011-12-14 10:33:02'
with t as
(select a.id aid,b.id bid,
isnull(datediff(mi,b.udate,a.udate),0) dt
from inghot a
left join inghot b on a.id=b.id+1
),
t1 as
(select t0.aid,t0.bid,t0.dt,
(select sum(t2.dt) from t t2 where t2.aid<=t0.aid) sdt
from t t0
)
select b.*
from t1 a,inghot b
where a.aid=b.id and
(a.sdt>15 or a.dt>15 or a.aid=1)id udate
----------- -----------------------
1 2011-12-14 10:00:01.000
3 2011-12-14 10:16:01.000
4 2011-12-14 10:33:02.000(3 row(s) affected)
if not object_id(N'Tempdb..#TT') is null
drop table #TT
Go
Create table #TT([Col1] int,[Col2] Datetime)
Insert #TT
select 1,'2011-11-27 10:15:36.000' union all
select 2,'2011-11-27 10:15:40.000' union all
select 3,'2011-11-27 10:15:44.000' union all
select 4,'2011-11-27 10:28:32.000' union all
select 5,'2011-11-27 10:29:54.000' union all
select 6,'2011-11-27 10:29:58.000' union all
select 7,'2011-11-27 10:30:02.000' union all
select 8,'2011-11-27 10:33:06.000' union all
select 9,'2011-11-27 10:33:08.000' union all
select 10,'2011-11-27 10:41:00.000' union all
select 11,'2011-11-27 10:48:58.000' union all
select 12,'2011-11-27 10:53:20.000' union all
select 13,'2011-11-27 10:53:24.000' union all
select 14,'2011-11-27 10:56:54.000' union all
select 15,'2011-11-27 11:04:28.000' union all
select 16,'2011-11-27 11:04:32.000' union all
select 17,'2011-11-27 11:04:34.000' union all
select 18,'2011-11-27 11:21:40.000' union all
select 19,'2011-11-27 11:21:46.000' union all
select 20,'2011-11-27 11:21:46.000'
Go
先在这里谢过了。
drop table tb
Go
Create table tb(id int,dt Datetime)
Insert tb
select 1,'2011-11-27 10:15:36.000' union all
select 2,'2011-11-27 10:15:40.000' union all
select 3,'2011-11-27 10:15:44.000' union all
select 4,'2011-11-27 10:28:32.000' union all
select 5,'2011-11-27 10:29:54.000' union all
select 6,'2011-11-27 10:29:58.000' union all
select 7,'2011-11-27 10:30:02.000' union all
select 8,'2011-11-27 10:33:06.000' union all
select 9,'2011-11-27 10:33:08.000' union all
select 10,'2011-11-27 10:41:00.000' union all
select 11,'2011-11-27 10:48:58.000' union all
select 12,'2011-11-27 10:53:20.000' union all
select 13,'2011-11-27 10:53:24.000' union all
select 14,'2011-11-27 10:56:54.000' union all
select 15,'2011-11-27 11:04:28.000' union all
select 16,'2011-11-27 11:04:32.000' union all
select 17,'2011-11-27 11:04:34.000' union all
select 18,'2011-11-27 11:21:40.000' union all
select 19,'2011-11-27 11:21:46.000' union all
select 20,'2011-11-27 11:21:46.000'
Goselect *,null as into #tb from tb
declare @dt1 datetime=(select MIN(dt) from tb),@dt2 datetime
update #tb set =case when DATEDIFF(MI,@dt1,dt)>15 then 1 end,
@dt1=case when DATEDIFF(mi,@dt1,@dt2)>15 then @dt2 else @dt1 end,@dt2=dt
select top 1 id,dt from #tb
union all
select id,dt from #tb where =1
drop table tb,#tb /*
id dt
----------- -----------------------
1 2011-11-27 10:15:36.000
8 2011-11-27 10:33:06.000
12 2011-11-27 10:53:20.000
18 2011-11-27 11:21:40.000(4 行受影响)