27,580
社区成员
发帖
与我相关
我的任务
分享
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
这个是我用db2写的一个sql,timestampdiff(2,char(timestamp(EnTime)-timestamp(a.EnTime)))<=10 这个是比对小于10s的,记录表里有2条这个数据,但是结果里却有第一条数据,难道我哪里写错了?
'WJ0812110' '2013-05-01 17:01:00.000000' 1098937908
'WJ0812110' '2013-05-01 17:00:03.000000' 0
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
*/
-- 建测试表
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)
*/
----------------------------------------------------------------
-- 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 行受影响)
*/