• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

求SQL语句。

gt1984 2008-03-22 11:56:07
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查询语句怎么实现呢。

...全文
49 点赞 收藏 3
写回复
3 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
gahade 2008-03-22

--创建测试数据
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 #
回复
hery2002 2008-03-22

use [csdn]
go
if object_id(N'tbl') is not null
begin
print 'drop table tbl .... '
drop table tbl
end
go

create 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')
go

use [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
end

select identity(int,1,1) as sid, cardno,perno, PressDate, DoorNo into #tmp1 from tbl where status ='0' order by cardno,perno,recno

select 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

回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-22 11:56
社区公告
暂无公告