【求助】一条简单Sql语句

Question

select * from CardEvent  t1 (nolock)
where convert(varchar(10),eventDateTime,120)='2014-05-01'
and exists(select 1 from CardEvent   t2 (nolock) where convert(varchar(10),t2.eventDateTime,120)='2014-05-01'
and t2.personId=t1.personId and (t2.doorType=1 or t2.doorType=3))
and not exists(select 1 from CardEvent   t3 (nolock) where convert(varchar(10),t3.eventDateTime,120)='2014-05-01'
and t3.personId=t1.personId and t2.doorType<>2and t2.doorType<>4))

Answer 1

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-05-20 15:04:12
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
-- Apr  2 2010 15:48:46 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[CardEvent]
if object_id('[CardEvent]') is not null drop table [CardEvent]
go 
create table [CardEvent]([eventDateTime] datetime,[personId] int,[personName] nvarchar(2),[areaId] int,[areaName] nvarchar(4),[doorId] int,[doorName] nvarchar(6),[doorType] int)
insert [CardEvent]
select '2014-05-01 10:00:00',1,'A',1,N'一层',1,N'东门外',1 union all
select '2014-05-01 10:03:00',1,'A',1,N'一层',1,N'东门外',2 union all
select '2014-05-01 10:20:00',1,'A',1,N'一层',1,N'东门外',1 union all
select '2014-05-01 10:21:00',1,'A',1,N'一层',2,N'东门内',3 union all
select '2014-05-01 10:23:00',1,'A',1,N'一层',2,N'东门内',4 union all
select '2014-05-01 10:30:00',1,'A',1,N'一层',2,N'东门内',3 union all
select '2014-05-01 10:40:00',1,'A',1,N'一层',3,N'西门外',1 union all
select '2014-05-01 10:45:00',1,'A',1,N'一层',3,N'西门外',2 union all
select '2014-05-01 10:56:00',1,'A',1,N'一层',3,N'西门外',1 union all
select '2014-05-01 10:35:00',2,'B',1,N'一层',4,N'西门内',3 union all
select '2014-05-01 10:38:00',2,'B',1,N'一层',4,N'西门内',4 union all
select '2014-05-01 10:55:00',2,'B',1,N'二层',4,N'西门内',3
--------------生成数据--------------------------SELECT  a.*
FROM    [CardEvent] A 
WHERE a.doortype NOT  IN (2,4) AND   EXISTS (SELECT 1 FROM [CardEvent] b WHERE a.personid=b.personid AND b.doortype IN (1,3) AND b.[eventDateTime]<a.[eventDateTime] 
AND a.doorid=b.doorid
)----------------结果----------------------------
/* 
eventDateTime           personId    personName areaId      areaName doorId      doorName doorType
----------------------- ----------- ---------- ----------- -------- ----------- -------- -----------
2014-05-01 10:20:00.000 1           A          1           一层       1           东门外      1
2014-05-01 10:30:00.000 1           A          1           一层       2           东门内      3
2014-05-01 10:56:00.000 1           A          1           一层       3           西门外      1
2014-05-01 10:55:00.000 2           B          1           二层       4           西门内      3*/

Answer 2

没看懂你这个。
你这个例子能不能说的更具体点，看到你这个 就是doorType=1,3 

Answer 3

  select * from 
(select * ,row_number() over (partition by personId,doorType,doorId  order by eventDateTime desc) as m 
from CardEvent) a where m=1 and doorType in ('1','3')/*
eventDateTime           personId    personName areaId      areaName doorId      doorName doorType    m
----------------------- ----------- ---------- ----------- -------- ----------- -------- ----------- --------------------
2014-05-01 10:20:00.000 1           A          1           一层       1           东门外      1           1
2014-05-01 10:56:00.000 1           A          1           一层       3           西门外      1           1
2014-05-01 10:30:00.000 1           A          1           一层       2           东门内      3           1
2014-05-01 10:55:00.000 2           B          1           二层       4           西门内      3           1*/

Answer 4

--借用版主的数据
if object_id('[CardEvent]') is not null drop table [CardEvent]
go 
create table [CardEvent]([eventDateTime] datetime,[personId] int,[personName] nvarchar(2),[areaId] int,[areaName] nvarchar(4),[doorId] int,[doorName] nvarchar(6),[doorType] int)
insert [CardEvent]
select '2014-05-01 10:00:00',1,'A',1,N'一层',1,N'东门外',1 union all
select '2014-05-01 10:03:00',1,'A',1,N'一层',1,N'东门外',2 union all
select '2014-05-01 10:20:00',1,'A',1,N'一层',1,N'东门外',1 union all
select '2014-05-01 10:21:00',1,'A',1,N'一层',2,N'东门内',3 union all
select '2014-05-01 10:23:00',1,'A',1,N'一层',2,N'东门内',4 union all
select '2014-05-01 10:30:00',1,'A',1,N'一层',2,N'东门内',3 union all
select '2014-05-01 10:40:00',1,'A',1,N'一层',3,N'西门外',1 union all
select '2014-05-01 10:45:00',1,'A',1,N'一层',3,N'西门外',2 union all
select '2014-05-01 10:56:00',1,'A',1,N'一层',3,N'西门外',1 union all
select '2014-05-01 10:35:00',2,'B',1,N'一层',4,N'西门内',3 union all
select '2014-05-01 10:38:00',2,'B',1,N'一层',4,N'西门内',4 union all
select '2014-05-01 10:55:00',2,'B',1,N'二层',4,N'西门内',3--开始查询
 select * from 
(select * ,row_number() over (partition by personId,doorType,doorId  order by eventDateTime desc) as m 
from CardEvent) a where m=1 and doorType in ('1','3') 
order by personId , eventDateTime--结果
/*
eventDateTime           personId    personName areaId      areaName doorId      doorName doorType    m
----------------------- ----------- ---------- ----------- -------- ----------- -------- ----------- --------------------
2014-05-01 10:20:00.000 1           A          1           一层       1           东门外      1           1
2014-05-01 10:30:00.000 1           A          1           一层       2           东门内      3           1
2014-05-01 10:56:00.000 1           A          1           一层       3           西门外      1           1
2014-05-01 10:55:00.000 2           B          1           二层       4           西门内      3           1*/

Answer 5

--不好意思，我上面是错的
select * from CardEvent  t1 (nolock)
where convert(varchar(10),eventDateTime,120)='2014-05-01' 
AND t1.doorType<>2 AND t1.doorType<>4 --未出去的
--且有进来过的
and exists(select 1 from CardEvent   t2 (nolock) where convert(varchar(10),t2.eventDateTime,120)='2014-05-01'
and t2.personId=t1.personId AND t2.doorId=t1.doorId AND t2.eventDateTime>t1.eventDateTime and (t2.doorType=1 or t2.doorType=3))
--进来又出去的记录则不用显示
AND NOT EXISTS(SELECT 1 FROM CardEvent t3 (nolock)
WHERE convert(varchar(10),t3.eventDateTime,120)='2014-05-01' AND (t1.doorType=1 or t1.doorType=3)
AND t3.personId=t1.personId AND t3.doorId=t1.doorId
AND EXISTS(SELECT 1 FROM CardEvent t4 (nolock) 
WHERE convert(varchar(10),t4.eventDateTime,120)='2014-05-01' 
AND (t1.doorType=2 or t1.doorType=4) AND t4.personId=t3.personId AND t4.doorId=t3.doorId 
AND t4.eventDateTime>t3.eventDateTime))

Answer 6

--晕，应该是小于号才对
select * from CardEvent  t1 (nolock)
where convert(varchar(10),eventDateTime,120)='2014-05-01' 
AND t1.doorType<>2 AND t1.doorType<>4 --未出去的
--且有进来过的
and exists(select 1 from CardEvent   t2 (nolock) where convert(varchar(10),t2.eventDateTime,120)='2014-05-01'
and t2.personId=t1.personId AND t2.doorId=t1.doorId AND t2.eventDateTime<t1.eventDateTime and (t2.doorType=1 or t2.doorType=3))
--进来又出去的记录则不用显示
AND NOT EXISTS(SELECT 1 FROM CardEvent t3 (nolock)
WHERE convert(varchar(10),t3.eventDateTime,120)='2014-05-01' AND (t1.doorType=1 or t1.doorType=3)
AND t3.personId=t1.personId AND t3.doorId=t1.doorId
AND EXISTS(SELECT 1 FROM CardEvent t4 (nolock) 
WHERE convert(varchar(10),t4.eventDateTime,120)='2014-05-01' 
AND (t1.doorType=2 or t1.doorType=4) AND t4.personId=t3.personId AND t4.doorId=t3.doorId 
AND t4.eventDateTime<t3.eventDateTime))