SQL语句菜鸟问题，高手们进来抢分啦！！！！！

AskValue 2004-11-22 09:08:03

表结构如下：
CardNO IOtime TYPE
001 13:20:30 IN
002 13:25:20 OUT
002 13:26:22 IN
001 13:27:30 OUT
003 13:28:01 IN
001 13:29:56 IN
001 13:30:33 OUT
用一段Access数据库的SQL语句求出每一个编号最大时间值并且TYPE值为"IN"，结果按CardNO升序排列。即按上表结果应为：
CardNO IOtime TYPE
002 13:26:22 IN
003 13:28:01 IN

...全文

239 23 打赏收藏转发到动态举报

写回复

用AI写文章

23 条回复

切换为时间正序

请发表友善的回复…

发表回复

long111 2004-11-24

打赏
举报

create table t(cardNO char(8) ,IOtime datetime, TYPE char(4))
insert into t
select '001','13:20:30 ','IN' union all
select ' 002','13:25:20','OUT' union all
select '002','13:26:22','IN' union all
select '001','13:27:30','OUT' union all
select '003','13:28:01','IN' union all
select '001','13:29:56','IN' union all
select '001','13:30:33','OUT'
go

select *
from t t1
where IOtime=(select max(IOtime) from t where t.cardNO=t1.cardNO)
and TYPE='IN'
order by cardNO asc

drop table t
我的方法和楼上的一样啊

liao5288 2004-11-23

打赏
举报

select * from temp as t1
where
iotime=(select max(iotime) from tmpe
where cardno=t1.cardno) and
type='in'
order by cardno
我试过了，这种方法能行！

futulove 2004-11-22

打赏
举报

偶抢

zhang_yzy 2004-11-22

打赏
举报

感觉还是有些问题，
改一下
select b.*
from (select cardno,max(iotime) as iotem
from temp
group by cardno ) a inner join temp b on b.cardno=a.cardno and b.iotime=a.iotime
where [type]='IN'

zhang_yzy 2004-11-22

打赏
举报

楼上写的正确，不过应该注意一下，在 ACCESS 中TYPE是关键字，改一下为：
select *
from temp a
where exists(select 1
from ( select cardno,max(iotime) as iotime1
from temp
group by cardno
) b
where a.cardno=b.cardno
and a.iotime=b.iotime1
)
and [type]='IN'

jackluo1981 2004-11-22

打赏
举报

To:qizhanfeng(glacier)
不好意思，你的是对的！

jackluo1981 2004-11-22

打赏
举报

谁来运行一下上面的代码，我怎么运行出来的不是
002 13:26:22 IN
003 13:28:01 IN

qizhanfeng 2004-11-22

打赏
举报

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

jackluo1981 2004-11-22