一个序号字段int,一个时间字段datetime
怎么将时间字段在两分钟内,序号字段存在三个数据或以上的选择出来?
怎么将时间字段在两分钟内,序号字段存在三个数据或以上的选择出来?
解决方案 »
- 帮忙写一个排名的SQL语句
- SQL 里面怎么查询是本周??????
- SQL Server 2005 在开发与DBA方面的改进
- SQL2005触发器
- Microsoft OLE DB Provider for SQL Server (0x80040E07)
- 怎么写这个sql语句??
- 怎么查询重复数据~~求救!!!!!!!!
- 如何复制一个数据库,但不需要其中的数据。
- 急急急 我的数据库先分离了,然后用以前的日志文件复盖了现在的日志文件,不能附加了
- 如何解决触发器死循环的问题!
- 自由求和公式
- 请问,在sql server中我用reindex重建索引后,扫描密度仍旧没有变化,达不到100%,应如何处理
set @time = convert(varchar(10),getdate(),120)
while @time < 某个时间
begin
select *
from tb
where 时间字段 between @time and dateadd(mm,2,@time) adn len(序号字段)>3
set @time = dateadd(mm,2,@time)
end
from @s a
group by id,d
having (select count(1) from @s where id != a.id and datediff(mi,d,a.d) < 2)>=3
(select 1 from 表 where DATEDIFF(Mi,时间,a.时间)>2 )
and ID in (
select id from 表
group by id having COUNT(*)>=3)
(select 1 from 表 where a.id=id and DATEDIFF(Mi,时间,a.时间)>2 )
and ID in (
select id from 表
group by id having COUNT(*)>=3)
select * from tb a where
(select count(1) from tb where abs(DATEDIFF(Mi,时间,a.时间))<=2 )
) >=3
例如,原表a中不排序,排序后数据如下
p_num p_time
……
100126 2008-09-09 17:20:00.000
100127 2008-09-09 17:22:00.000
100128 2008-09-09 17:24:00.000
100129 2008-09-09 17:27:00.000
100130 2008-09-09 18:01:00.000
100131 2008-09-09 18:02:00.000
100132 2008-09-09 18:03:00.000
100133 2008-09-09 18:04:00.000
100134 2008-09-10 18:05:00.000
100135 2008-09-10 18:06:00.000
100136 2008-09-10 18:07:00.000
……
100130-100136 均符合条件,怎样做,各位辛苦!
go
create table tb([p_num] int,[p_time] datetime)
insert tb select 100126,'2008-09-09 17:20:00.000'
union all select 100127,'2008-09-09 17:22:00.000'
union all select 100128,'2008-09-09 17:24:00.000'
union all select 100129,'2008-09-09 17:27:00.000'
union all select 100130,'2008-09-09 18:01:00.000'
union all select 100131,'2008-09-09 18:02:00.000'
union all select 100132,'2008-09-09 18:03:00.000'
union all select 100133,'2008-09-09 18:04:00.000'
union all select 100134,'2008-09-10 18:05:00.000'
union all select 100135,'2008-09-10 18:06:00.000'
union all select 100136,'2008-09-10 18:07:00.000'
go
alter table tb add fid int
go
declare @t datetime,@i int
set @i=0
update tb set fid=@i,@i=case when datediff(n,@t,p_time)<2 then @i else @i+1 end,@t=p_time
go
select p_num,p_time from tb where fid in (select fid from tb group by fid having count(1)>2)
/*
p_num p_time
----------- -----------------------
100130 2008-09-09 18:01:00.000
100131 2008-09-09 18:02:00.000
100132 2008-09-09 18:03:00.000
100133 2008-09-09 18:04:00.000
100134 2008-09-10 18:05:00.000
100135 2008-09-10 18:06:00.000
100136 2008-09-10 18:07:00.000(7 行受影响)
*/
alter table tb drop column fid
go
create table test(p_num int , p_time datetime)
go
insert test select 100126, '2008-09-09 17:20:00.000'
insert test select 100127, '2008-09-09 17:22:00.000'
insert test select 100128, '2008-09-09 17:24:00.000'
insert test select 100129, '2008-09-09 17:27:00.000'
insert test select 100130 ,'2008-09-09 18:01:00.000'
insert test select 100131 ,'2008-09-09 18:02:00.000'
insert test select 100132 ,'2008-09-09 18:03:00.000'
insert test select 100133 ,'2008-09-09 18:04:00.000'
insert test select 100134 ,'2008-09-10 18:05:00.000'
insert test select 100135 ,'2008-09-10 18:06:00.000'
insert test select 100136 ,'2008-09-10 18:07:00.000'
goselect a.* from test a where (select count(1) from test where a.p_time between p_time and dateadd(mi,2,p_time))>=3
union
select a.* from test a where (select count(1) from test where p_time between a.p_time and dateadd(mi,2,a.p_time))>=3
union
select a.* from test a,test b ,test c
where b.p_num=a.p_num-1 and c.p_num=a.p_num+1 and datediff(mi,b.p_time,c.p_time)<=2
/*
p_num p_time
----------- ------------------------------------------------------
100130 2008-09-09 18:01:00.000
100131 2008-09-09 18:02:00.000
100132 2008-09-09 18:03:00.000
100133 2008-09-09 18:04:00.000
100134 2008-09-10 18:05:00.000
100135 2008-09-10 18:06:00.000
100136 2008-09-10 18:07:00.000(所影响的行数为 7 行)
*/drop table test
DECLARE @TB TABLE(ID int, DAT DATETIME)
INSERT @TB
select '100126', '2008-09-09 17:20:00.000'
union all select '100127', '2008-09-09 17:22:00.000'
union all select '100128', '2008-09-09 17:24:00.000'
union all select '100129', '2008-09-09 17:27:00.000'
union all select '100130', '2008-09-09 18:01:00.000'
union all select '100131', '2008-09-09 18:02:00.000'
union all select '100132', '2008-09-09 18:03:00.000'
union all select '100133', '2008-09-09 18:04:00.000'
union all select '100134', '2008-09-10 12:16:00.000'
union all select '100135', '2008-09-10 12:17:00.000'
union all select '100136', '2008-09-10 12:18:00.000'SELECT * FROM @TB A WHERE EXISTS(SELECT 1 FROM(
SELECT DAT FROM @TB A WHERE EXISTS(SELECT 1 FROM @TB B WHERE B.DAT BETWEEN A.DAT AND DATEADD(MI, 2, A.DAT) HAVING COUNT(1)>=3)
) B WHERE A.DAT BETWEEN B.DAT AND DATEADD(MI, 2, B.DAT))
go
insert test select 100126, '2008-09-09 17:20:00.000'
insert test select 100127, '2008-09-09 17:22:00.000'
insert test select 100128, '2008-09-09 17:24:00.000'
insert test select 100129, '2008-09-09 17:27:00.000'
insert test select 100130 ,'2008-09-09 18:01:00.000'
insert test select 100131 ,'2008-09-09 18:02:00.000'
insert test select 100132 ,'2008-09-09 18:03:00.000'
insert test select 100133 ,'2008-09-09 18:04:00.000'
insert test select 100134 ,'2008-09-10 18:05:00.000'
insert test select 100135 ,'2008-09-10 18:06:00.000'
insert test select 100136 ,'2008-09-10 18:07:00.000'
go
select distinct b.* from
(
select p_time1,p_time2 from
(
select * from
(
select a.p_time as p_time1,b.p_time as p_time2 from test a,test b
where a.p_time <b.p_time and datediff(mi,a.p_time,b.p_time) <=2
) a
left join test b on b.p_time between a.p_time1 and a.p_time2
) c
group by p_time1,p_time2
having Count(*)>=3
) a
left join test b on b.p_time between a.p_time1 and a.p_time2
from @s a
group by id,d
having (select count(1) from @s where id != a.id and datediff(mi,d,a.d) < 2)>=3