筛选表里面EnCardID不相同，EnTime相差不超过10分钟的记录
我有一个ENList表，里面存的都是一些入口记录，每条记录都有VehPlate,EnCardID,EnTime等信息，我现在想找到对于一个VehPlate来说EnCardID不相同，EnTime相差不超过10分钟的记录。
VehPlate 车牌
EnCardID 入口时所使用的卡
EnTime 入口时间
简单之就是找同一个车辆，在间隔不到10分钟的时间内使用多次不同卡进入的记录。
用C#我可以完成此功能，但是考虑到数据比较大，用存储过程可能会降低点执行时间。
我碰到的难点：由于需要对同一个车的相邻记录进行比对，用C#时，用for取i,i+1条记录就可以的，但是SQL 里的for 好像不支持索引吧？
[解决办法]
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2013-11-21 17:25:02
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
--Feb 10 2012 19:39:15
--Copyright (c) Microsoft Corporation
--Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([vehPlate] int,[enCardID] varchar(3),[EnTime] varchar(16))
insert [tb]
select 111111,'c1','2013-11-21 17:11' union all
select 111111,'c1','2013-11-21 17:15' union all
select 111111,'c2','2013-11-21 17:15' union all
select 222222,'c3','2013-11-21 17:13' union all
select 222222,'c4','2013-11-22 17:13' union all
select 222222,'c5','2013-11-21 17:09' union all
select 333333,'c9','2013-11-22 17:08' union all
select 444444,'c10','2013-11-22 17:09'
--------------开始查询--------------------------
SELECT
*
FROM
TB a
WHERE
EXISTS(SELECT 1 FROM TB WHERE vehPlate=a.vehPlate AND enCardID<>a.enCardID AND DATEDIFF(mi,EnTime,a.EnTime)<=10)
AND
NOT EXISTS(SELECT 1 FROM TB WHERE vehPlate=a.vehPlate AND enCardID=a.enCardID AND EnTime<a.EnTime)
----------------结果----------------------------
/* vehPlate enCardID EnTime
----------- -------- ----------------
111111 c1 2013-11-21 17:11
111111 c2 2013-11-21 17:15
222222 c3 2013-11-21 17:13
222222 c5 2013-11-21 17:09
(4 行受影响)
*/
-- 建测试表
create table ENList
(VehPlate varchar(10),EnCardID varchar(10),EnTime varchar(20))
insert into ENList
select '111111','c1','2013-11-21-17.11' union all
select '111111','c1','2013-11-21-17.15' union all
select '111111','c2','2013-11-21-17.15' union all
select '222222','c3','2013-11-21-17.13' union all
select '222222','c4','2013-11-22-17.13' union all
select '222222','c5','2013-11-21-17.09' union all
select '333333','c9','2013-11-22-17.08' union all
select '444444','c10','2013-11-22-17.09'
-- 建目标表
create table EnListException
(VehPlate varchar(10),EnCardID varchar(10),EnTime varchar(20))
with t as
(select VehPlate,EnCardID,EnTime,
cast(left(EnTime,10)+' '+replace(right(EnTime,5),'.',':') as datetime) 'EnTime2'
from ENList),
u as
(select VehPlate,EnCardID,EnTime,EnTime2,
row_number() over(partition by VehPlate order by EnTime2 desc) 'rn'
from t)
insert into EnListException(VehPlate,EnCardID,EnTime)
select a.VehPlate,a.EnCardID,a.EnTime
from u a
left join u b on a.VehPlate=b.VehPlate and a.rn=b.rn+1
where datediff(m,a.EnTime2,b.EnTime2)<10
order by a.VehPlate,a.EnCardID
-- 结果
select * from EnListException
/*
VehPlate EnCardID EnTime
---------- ---------- --------------------
111111 c1 2013-11-21-17.11
111111 c2 2013-11-21-17.15
222222 c3 2013-11-21-17.13
222222 c5 2013-11-21-17.09
(4 row(s) affected)
*/
create table ENList
(VehPlate varchar(10),EnCardID varchar(10),EnTime varchar(20))
insert into ENList
select '111111','c1','2013-11-21-17.11' union all
select '111111','c1','2013-11-21-17.15' union all
select '111111','c2','2013-11-21-17.15' union all
select '222222','c3','2013-11-21-17.13' union all
select '222222','c4','2013-11-22-17.13' union all
select '222222','c5','2013-11-21-17.09' union all
select '333333','c9','2013-11-22-17.08' union all
select '444444','c10','2013-11-22-17.09'
-- 建目标表
create table EnListException
(VehPlate varchar(10),EnCardID varchar(10),EnTime varchar(20))
;with t
as
(
select *,
replace(stuff(EnTime,len(EnTime)-CHARINDEX('-',reverse(EnTime))+1,1,' '),
'.',':') as t_EnTime,
row_number() over(partition by VehPlate
order by EnTime desc) as rownum
from ENList
)
insert into EnListException
select t1.VehPlate,t1.EnCardID,t1.EnTime
from t t1
inner join t t2
on t1.vehPlate = t2.VehPlate
and t1.rownum = t2.rownum + 1
and DATEDIFF(MINUTE,t2.t_enTime,t1.t_EnTime) < 10
order by t1.VehPlate,t1.EnCardID
select * from EnListException
/*
VehPlateEnCardIDEnTime
111111c12013-11-21-17.11
111111c22013-11-21-17.15
222222c32013-11-21-17.13
222222c52013-11-21-17.09
*/
