datediff与group by结合使用的问题 实在难住了···

Question

   
 问题：  
有一个表alatmp中含有200辆车的共2300条超速记录，记录由电脑自动生成，时间精确到秒，如   ID   veheclenumber       alaTime (报警时间)   498  陕A04480 2009-09-30 09:00:29
   497  陕A04480 2009-09-30 08:59:29
   496  陕A04480 2009-09-30 08:58:59
   494  陕A04297 2009-09-30 08:58:30
   493  陕A04480 2009-09-30 08:58:29
   492  陕A04480 2009-09-30 08:55:04  ----- 间隔太短，只保留任意一条
   491  陕A04480 2009-09-30 08:55:03  ----- 间隔太短，只保留任意一条
   490  陕A04480 2009-09-30 08:55:01  ----- 间隔太短，只保留任意一条
   489  陕A04297 2009-09-30 08:54:30以上记录中，同一辆车的两条超速记录时间间隔太短，现在要求把同一辆车报警记录中间隔时间少于5秒的归为1条，即如果同一辆车如果有多条报警记录时间间隔少于5秒，则只保留其中任意一条，其他的5秒以内的全部删除。如上表整理有应得的以下表：   498  陕A04480 2009-09-30 09:00:29
   497  陕A04480 2009-09-30 08:59:29
   496  陕A04480 2009-09-30 08:58:59
   494  陕A04297 2009-09-30 08:58:30
   493  陕A04480 2009-09-30 08:58:29
   492  陕A04480 2009-09-30 08:55:04
   489  陕A04297 2009-09-30 08:54:30
我首先做了一个查询：select id,veheclenumber as '车牌',alatime as '报警时间' from alatmp t where  exists (select max(id) from alatmp where  id<t.id group by veheclenumber,alatime having datediff(ss, alatime,t.alatime)<5)显示的结果应该是我需要删除的记录，但发现 494  陕A04297 2009-09-30 08:58:30 这一条也在查询的结果中，虽然这一条记录和上一条的记录时间间隔只有1秒，但由于不属于同一辆车，所以不能删除。问题就在这里，各位高手给看下，谢啦，当然我做的这个查询还不符合最终的要求 我的想法是，先把同一辆车少于5秒的查出来，然后删除···

Answer 1

select *
from alatmp a
where not exists(select * from alatmp
 where  A.veheclenumber=veheclenumber and DATEDIFF(MINUTE,A.alaTime,alaTime)=0 and Id<A.id) 

Answer 2

删除 
delete a 
from alatmp a
where id<>(select MAX(id) from alatmp
 where  A.veheclenumber=veheclenumber and DATEDIFF(MINUTE,A.alaTime,alaTime)=0 ) 

Answer 3

---查询 
select
  *
from 
  alatmp t
where 
  not exists
(select * from alatmp where  veheclenumber=t.veheclenumber and datediff(mi,t.alaTime,alaTime)=0 and Id<t.id)

Answer 4

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-11-04 21:20:25
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[alatmp]
if object_id('[alatmp]') is not null drop table [alatmp]
go 
create table [alatmp]([ID] int,[veheclenumber] varchar(8),[alaTime] datetime)
insert [alatmp]
select 498,'陕A04480','2009-09-30 09:00:29' union all
select 497,'陕A04480','2009-09-30 08:59:29' union all
select 496,'陕A04480','2009-09-30 08:58:59' union all
select 494,'陕A04297','2009-09-30 08:58:30' union all
select 493,'陕A04480','2009-09-30 08:58:29' union all
select 492,'陕A04480','2009-09-30 08:55:04' union all
select 491,'陕A04480','2009-09-30 08:55:03' union all
select 490,'陕A04480','2009-09-30 08:55:01' union all
select 489,'陕A04297','2009-09-30 08:54:30'
--------------开始查询--------------------------
select
  *
from 
  alatmp t
where 
  not exists
(select * from alatmp where  veheclenumber=t.veheclenumber and datediff(mi,t.alaTime,alaTime)=0 and Id<t.id)----------------结果----------------------------
/*ID          veheclenumber alaTime
----------- ------------- -----------------------
498         陕A04480       2009-09-30 09:00:29.000
497         陕A04480       2009-09-30 08:59:29.000
494         陕A04297       2009-09-30 08:58:30.000
493         陕A04480       2009-09-30 08:58:29.000
490         陕A04480       2009-09-30 08:55:01.000
489         陕A04297       2009-09-30 08:54:30.000(6 行受影响) 
*/

Answer 5

---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2009-11-04 21:18:30
---------------------------------
 
IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
go
CREATE TABLE [tb] (ID INT,veheclenumber VARCHAR(8),alaTime DATETIME)
INSERT INTO [tb]
SELECT 498,'陕A04480','2009-09-30 09:00:29' UNION ALL
SELECT 497,'陕A04480','2009-09-30 08:59:29' UNION ALL
SELECT 496,'陕A04480','2009-09-30 08:58:59' UNION ALL
SELECT 494,'陕A04297','2009-09-30 08:58:30' UNION ALL
SELECT 493,'陕A04480','2009-09-30 08:58:29' UNION ALL
SELECT 492,'陕A04480','2009-09-30 08:55:04' UNION ALL
SELECT 491,'陕A04480','2009-09-30 08:55:03' UNION ALL
SELECT 490,'陕A04480','2009-09-30 08:55:01' UNION ALL
SELECT 489,'陕A04297','2009-09-30 08:54:30'select * from [tb]delete t
from tb s join tb t on s.veheclenumber=t.veheclenumber and s.id=t.id+1
where datediff(ss,t.alatime,s.alatime)<5select * from tb498 陕A04480 2009-09-30 09:00:29.000
497 陕A04480 2009-09-30 08:59:29.000
496 陕A04480 2009-09-30 08:58:59.000
494 陕A04297 2009-09-30 08:58:30.000
493 陕A04480 2009-09-30 08:58:29.000
492 陕A04480 2009-09-30 08:55:04.000
489 陕A04297 2009-09-30 08:54:30.000

Answer 6

哇~~刚去吃了个饭，回来一看就这么多回复，感激ING...，正在验证结果，稍后散分，谢谢~~

Answer 7

TO  poofly大哥和小F: datediff(mi,A.alaTime,alaTime)=0中 A.alatime和alatime顺序写反啦吧？我的表中有2476条数据，顺序不一样，查询结果相差太远啦~~ 呵呵 另外我是改成
datediff(ss,alaTime,A.alaTime)<5 来测试的，感觉查询的速度也很快~~总之谢谢了~~TO 让你望见影子的墙：谢谢~~

Answer 8

虽然已经结贴了。
看了这个子查询应该是这样吧，能得到你的结果。if object_id('[alatmp]') is not null drop table [alatmp]
go 
create table [alatmp]([ID] int,[veheclenumber] Nvarchar(8),[alaTime] datetime)
insert [alatmp]
select 498,N'陕A04480','2009-09-30 09:00:29' union all
select 497,N'陕A04480','2009-09-30 08:59:29' union all
select 496,N'陕A04480','2009-09-30 08:58:59' union all
select 494,N'陕A04297','2009-09-30 08:58:30' union all
select 493,N'陕A04480','2009-09-30 08:58:29' union all
select 492,N'陕A04480','2009-09-30 08:55:04' union all
select 491,N'陕A04480','2009-09-30 08:55:03' union all
select 490,N'陕A04480','2009-09-30 08:55:01' union all
select 489,N'陕A04297','2009-09-30 08:54:30'SELECT * FROM [alatmp] A
WHERE NOT EXISTS 
(
SELECT * FROM [alatmp] B
WHERE ID>A.ID AND [veheclenumber]=A.[veheclenumber] AND DATEDIFF(SS,A.[alaTime],[alaTime])<5
)