create table temp(CardNO char(8), IOtime datetime, TYPE char(4))
insert into temp values('001', #13:20:30#, 'IN')
insert into temp values('001', #13:20:30#, 'IN')
insert into temp values( '002', #13:25:20#, 'OUT')
insert into temp values( '002', #13:26:22#, 'IN')
insert into temp values( '001', #13:27:30#, 'OUT')
insert into temp values( '003', #13:28:01#, 'IN')
insert into temp values( '001', #13:29:56#, 'IN')
insert into temp values( '001', #13:30:33#, 'OUT')
测试
select * from temp
where
exists(select 1 from
(
SELECT cardno,max(iotime) as iotime1 from temp group by cardno
) A where cardno=A.cardno and iotime=A.iotime1
) and type='in'
结果
CardNO IOtime TYPE
002 13:26:22 IN
003 13:28:01 IN