• 主页
  • 基础类
  • 应用实例
  • 新技术前沿

【求助】一条简单Sql语句

xupeihuagudulei 2014-05-20 02:59:02


有一张刷卡事件表 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
...全文
103 点赞 收藏 8
写回复
8 条回复
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))
回复 点赞
洞房不敗 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

*/
回复 点赞
洞房不敗 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))
回复 点赞
发动态
发帖子
MS-SQL Server
创建于2007-09-28

1.4w+

社区成员

25.3w+

社区内容

MS-SQL Server相关内容讨论专区
社区公告
暂无公告