求SQL语句。

2008-03-22 11:56:07 104 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

gt1984 墨衍会员 · AI 创作全网分发关注 + 加入社区

基于SQL求集合的交、并、补对数据库查询结果集求补（差）、交、并集，可利用SQL的EXCEPT（补集）、INTERSECT（交集）和UNION（并集）关键词，其中Mysql仅支持UNION，但可以利用SQL实现EXCEPT、INTERSECT的功能。阅读详情

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查询语句怎么实现呢。

...全文

ADS中HB仿真自动优化报错问题解决心得在ADS HB仿真中自动优化出现报错被强制暂停，解决方法如下阅读详情

gt1984

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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

求SQL语句。

相关推荐