34,837
社区成员
发帖
与我相关
我的任务
分享【求助】一条简单Sql语句
有一张刷卡事件表 CardEvent
eventDateTime personId personName areaId areaName doorId doorName doorType
2014-05-01 10:00:00 1 A 1 一层 1 东门外 1
2014-05-01 10:03:00 1 A 1 一层 1 东门外 2
2014-05-01 10:20:00 1 A 1 一层 1 东门外 1
2014-05-01 10:21:00 1 A 1 一层 2 东门内 3
2014-05-01 10:23:00 1 A 1 一层 2 东门内 4
2014-05-01 10:30:00 1 A 1 一层 2 东门内 3
2014-05-01 10:40:00 1 A 1 一层 3 西门外 1
2014-05-01 10:45:00 1 A 1 一层 3 西门外 2
2014-05-01 10:56:00 1 A 1 一层 3 西门外 1
2014-05-01 10:35:00 2 B 1 一层 4 西门内 3
2014-05-01 10:38:00 2 B 1 一层 4 西门内 4
2014-05-01 10:55:00 2 B 1 二层 4 西门内 3
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
其中doorType 1、3 代表进门，2、4代表出门，现在要取出的数据为所有进门之后未出人员信息，数据如下：
2014-05-01 10:20:00 1 A 1 一层 1 东门外 1
2014-05-01 10:30:00 1 A 1 一层 2 东门内 3
2014-05-01 10:56:00 1 A 1 一层 3 西门外 1
2014-05-01 10:55:00 2 B 1 二层 4 西门内 3
...全文
请发表友善的回复…
发表回复
HUSTYZHY 2014-05-20
- 打赏
- 举报
顺路学习一下，
---涛声依旧--- 2014-05-20
- 打赏
- 举报
--晕，应该是小于号才对
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))
---涛声依旧--- 2014-05-20
- 打赏
- 举报
--不好意思，我上面是错的
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))
t101lian 2014-05-20
- 打赏
- 举报
--借用版主的数据
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
*/t101lian 2014-05-20
- 打赏
- 举报
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
*/
_mice 2014-05-20
- 打赏
- 举报
没看懂你这个。
你这个例子能不能说的更具体点，看到你这个 就是doorType=1,3
發糞塗牆 2014-05-20
- 打赏
- 举报
----------------------------------------------------------------
-- 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
*/
---涛声依旧--- 2014-05-20
- 打赏
- 举报
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))
