27,579
社区成员
发帖
与我相关
我的任务
分享筛选表里面EnCardID不相同，EnTime相差不超过10分钟的记录
我有一个ENList表，里面存的都是一些入口记录，每条记录都有VehPlate,EnCardID,EnTime等信息，我现在想找到对于一个VehPlate来说EnCardID不相同，EnTime相差不超过10分钟的记录。
VehPlate 车牌
EnCardID 入口时所使用的卡
EnTime 入口时间
简单之就是找同一个车辆，在间隔不到10分钟的时间内使用多次不同卡进入的记录。
用C#我可以完成此功能，但是考虑到数据比较大，用存储过程可能会降低点执行时间。
我碰到的难点：由于需要对同一个车的相邻记录进行比对，用C#时，用for取i,i+1条记录就可以的，但是SQL 里的for 好像不支持索引吧？
VehPlate 车牌
EnCardID 入口时所使用的卡
EnTime 入口时间
简单之就是找同一个车辆，在间隔不到10分钟的时间内使用多次不同卡进入的记录。
用C#我可以完成此功能，但是考虑到数据比较大，用存储过程可能会降低点执行时间。
我碰到的难点：由于需要对同一个车的相邻记录进行比对，用C#时，用for取i,i+1条记录就可以的，但是SQL 里的for 好像不支持索引吧？
...全文
请发表友善的回复…
发表回复
十八道胡同 2013-11-22
- 打赏
- 举报
SELECT
*
FROM
ENLISTVEHDB201305 a
WHERE
EXISTS(SELECT 1 FROM ENLISTVEHDB201305 WHERE ENVEHPLATE=a.ENVEHPLATE AND enCardID!=a.enCardID AND timestampdiff(2,char(timestamp(EnTime)-timestamp(a.EnTime)))<=10)
AND
NOT EXISTS(SELECT 1 FROM ENLISTVEHDB201305 WHERE ENVEHPLATE=a.ENVEHPLATE AND enCardID=a.enCardID AND EnTime<a.EnTime) order by ENVEHPLATE十八道胡同 2013-11-22
- 打赏
- 举报
我给的数据格式不符合SQl Server的要求，数据库内的数据是SQL Server的datetime类型的。
十八道胡同 2013-11-22
- 打赏
- 举报
谢谢各位的热心回复。
LongRui888 2013-11-21
- 打赏
- 举报
是这样吗：
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
/*
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
*/ 
唐诗三百首 2013-11-21
- 打赏
- 举报
-- 建测试表
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)
*/
--小F-- 2013-11-21
- 打赏
- 举报
----------------------------------------------------------------
-- 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 行受影响)
*/十八道胡同 2013-11-21
- 打赏
- 举报
vehPlate enCardID EnTime
111111 c1 2013-11-21-17.11
111111 c1 2013-11-21-17.15
111111 c2 2013-11-21-17.15
222222 c3 2013-11-21-17.13
222222 c4 2013-11-22-17.13
222222 c5 2013-11-21-17.09
333333 c9 2013-11-22-17.08
444444 c10 2013-11-22-17.09
假设表是这样的，那么最后EnListException 表里就有4条记录
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
十八道胡同 2013-11-21
- 打赏
- 举报
我的 C#的思路是这样的：
1，找到所有的vehPlate。
2，对于每个车牌，找到他的所有入口流水且按照entime降序，然后用for依次比对相邻的2个记录，看是不是enCardID不相同 且EnTime 相差不到10分钟的记录，这些记录先放dataTable里，等所有车牌走一遍了，在批量插入新的表EnListException.
我觉得用存储过程写思路和这个差不多吧 。
姐只会简单SQL语句 2013-11-21
- 打赏
- 举报
看的是懂非懂的，直接上数据。
