Question

asp提取数据库要输出最后时间状态为off的id要怎么写sql语句啊

一个表有id、state和time三个项 要输出最后时间state为off的id要怎么写sql语句啊
如：
id state time
002 on 2010-1-1
003 off 2010-1-2
002 off 2010-1-3
001 off 2010-1-4
003 on 2010-1-1

应该输出
id
001
002
如：
id state time
002 on 2010-1-1
003 off 2010-1-2
002 off 2010-1-3
001 off 2010-1-4
003 on 2010-1-5
最后一条时间写错了 是5号

select [id] from table where state='off' order by [time] desc 这样写只会把有off按时间全部输出吧 输出结果应该是
003
002
001

Answer 1

select a.id from [table] a inner join
(select id,max([time]) as max_time from [table] group by id) tbl
on a.id = tbl.id and a.time = tbl.max_time
where state = 'off' order by a.id

Answer 2

select [id] from table where state='off' order by [time] desc