cardno RecNo PressDate PerNo DoorNo Status
000001 1 2008-02-20 15:02:48.000 00000001 010 0
000003 10 2008-02-20 15:03:43.000 00000003 010 0
000001 2 2008-02-20 15:08:49.000 00000001 010 1
000001 3 2008-02-20 16:02:52.000 00000001 010 0
000001 4 2008-02-20 20:26:55.000 00000001 010 1
000003 11 2008-02-20 20:36:42.000 00000003 010 1
000002 8 2008-02-20 15:03:12.000 00000002 010 0
000002 9 2008-02-20 15:09:26.000 00000002 010 1 有如上记录,cardno为卡号, RecNo为流水编号, PressDate为进门时间, PerNo为人事编号, DoorNo为门编号,Status为进门状态(0:进 1:出)现在我想得到如下这样的查询结果
cardno PerNo PressDateIn PressDateOut DoorNo
000001 00000001 2008-02-20 15:02:48.000 2008-02-20 15:08:49.000 010
000001 00000001 2008-02-20 16:02:52.000 2008-02-20 20:36:42.000 010
000002 00000002 2008-02-20 15:03:12.000 2008-02-20 15:09:26.000 010
000003 00000003 2008-02-20 15:03:43.000 2008-02-20 20:36:42.000 010这样的SQL查询语句怎么实现呢。
000001 1 2008-02-20 15:02:48.000 00000001 010 0
000003 10 2008-02-20 15:03:43.000 00000003 010 0
000001 2 2008-02-20 15:08:49.000 00000001 010 1
000001 3 2008-02-20 16:02:52.000 00000001 010 0
000001 4 2008-02-20 20:26:55.000 00000001 010 1
000003 11 2008-02-20 20:36:42.000 00000003 010 1
000002 8 2008-02-20 15:03:12.000 00000002 010 0
000002 9 2008-02-20 15:09:26.000 00000002 010 1 有如上记录,cardno为卡号, RecNo为流水编号, PressDate为进门时间, PerNo为人事编号, DoorNo为门编号,Status为进门状态(0:进 1:出)现在我想得到如下这样的查询结果
cardno PerNo PressDateIn PressDateOut DoorNo
000001 00000001 2008-02-20 15:02:48.000 2008-02-20 15:08:49.000 010
000001 00000001 2008-02-20 16:02:52.000 2008-02-20 20:36:42.000 010
000002 00000002 2008-02-20 15:03:12.000 2008-02-20 15:09:26.000 010
000003 00000003 2008-02-20 15:03:43.000 2008-02-20 20:36:42.000 010这样的SQL查询语句怎么实现呢。
--创建测试数据
create table #(cardno char(6),RecNo int,PressDate datetime,PerNo char(8),DoorNo char(3),Status int)
insert into #
select '000001',1,'2008-02-20 15:02:48.000','00000001','010',0
union all select '000003',10,'2008-02-20 15:03:43.000','00000003','010',0
union all select '000001',2,'2008-02-20 15:08:49.000','00000001','010',1
union all select '000001',3,'2008-02-20 16:02:52.000','00000001','010',0
union all select '000001',4,'2008-02-20 20:26:55.000','00000001','010',1
union all select '000003',11,'2008-02-20 20:36:42.000','00000003','010',1
union all select '000002',8,'2008-02-20 15:03:12.000','00000002','010',0
union all select '000002',9,'2008-02-20 15:09:26.000','00000002','010',1 --查询
select a.cardno,
a.PerNo,
a.PressDate as 'PressDateIn',
(select min(PressDate) from # b where a.cardno=b.cardno and b.Status=1 and a.PressDate<b.PressDate) as 'PressDateOut',
a.DoorNo
from # a
where a.Status=0
order by a.cardno
/*
结果
cardno PerNo PressDateIn PressDateOut DoorNo
------ -------- ------------------------------------------------------ ------------------------------------------------------ ------
000001 00000001 2008-02-20 15:02:48.000 2008-02-20 15:08:49.000 010
000001 00000001 2008-02-20 16:02:52.000 2008-02-20 20:26:55.000 010
000002 00000002 2008-02-20 15:03:12.000 2008-02-20 15:09:26.000 010
000003 00000003 2008-02-20 15:03:43.000 2008-02-20 20:36:42.000 010(所影响的行数为 4 行)
*/--删除测试表
drop table #
use [csdn]
go
if object_id(N'tbl') is not null
begin
print 'drop table tbl .... '
drop table tbl
end
gocreate table tbl
(cardno varchar(7),
recno int,
pressdate datetime,
perno varchar(10),
doorno varchar(5),
status varchar(1)
)
go
use [csdn]
go
insert into tbl values ('000001','1','2008-02-20 15:02:48.000', '00000001', '010','0')
insert into tbl values ('000003', '10','2008-02-20 15:03:43.000', '00000003','010','0')
insert into tbl values ('000001', '2', '2008-02-20 15:08:49.000', '00000001', '010','1')
insert into tbl values ('000001', '3', '2008-02-20 16:02:52.000', '00000001', '010','0')
insert into tbl values ('000001', '4', '2008-02-20 20:26:55.000', '00000001', '010','1')
insert into tbl values ('000003', '11', '2008-02-20 20:36:42.000', '00000003','010','1')
insert into tbl values ('000002', '8', '2008-02-20 15:03:12.000', '00000002', '010','0')
insert into tbl values ('000002', '9', '2008-02-20 15:09:26.000', '00000002', '010','1')
gouse [csdn]
go
if object_id(N'tempdb..#tmp1') is not null
begin
print 'drop table tempdb..#tmp1 .... '
drop table tempdb..#tmp1
end
go
if object_id(N'tempdb..#tmp2') is not null
begin
print 'drop table tempdb..#tmp2 .... '
drop table tempdb..#tmp2
endselect identity(int,1,1) as sid, cardno,perno, PressDate, DoorNo into #tmp1 from tbl where status ='0' order by cardno,perno,recnoselect identity(int,1,1) as sid, cardno,perno, PressDate into #tmp2 from tbl where status ='1' order by cardno,perno,recno
select t1.cardno,t1.perno,t1.PressDate as PressDatteIn, t2.PressDate as PressDatteOut, t1.DoorNo
from #tmp1 t1, #tmp2 t2 where t1.cardno = t2.cardno and t1.perno = t2.perno and t1.sid = t2.sid