查询最新的几条记录？谢谢

[oSiGui]

表A：
id address state time
1 11 1 2006-11-21 09:00:01
2 22 1 2006-11-21 09:00:03
3 33 1 2006-11-21 10:00:03
4 11 0 2006-11-22 09:00:01
5 22 1 2006-11-22 09:00:03
7 11 1 2006-11-23 09:00:01

查询地址为11、22、33，时间最新的记录

结果：
3 33 1 2006-11-21 10:00:03
5 22 1 2006-11-22 09:00:03
7 11 1 2006-11-23 09:00:01

[卖水果的net]

create table test(id int,address int, state int, time datetime)
go
insert into test values
(1, 11, 1, '2006-11-21  09:00:01'),
(2, 22, 1, '2006-11-21  09:00:03'),
(3, 33, 1, '2006-11-21  10:00:03'),
(4, 11, 0, '2006-11-22  09:00:01'),
(5, 22, 1, '2006-11-22  09:00:03'),
(7, 11, 1, '2006-11-23  09:00:01')
go
with m as (
    select row_number() over(partition by address order by time) rn, *
    from test 
)
select * from m where rn = 1 
go
drop table test 
go

(6 行受影响)
rn                   id          address     state       time
-------------------- ----------- ----------- ----------- -----------------------
1                    1           11          1           2006-11-21 09:00:01.000
1                    2           22          1           2006-11-21 09:00:03.000
1                    3           33          1           2006-11-21 10:00:03.000

(3 行受影响)

[Tiger_Zhao]

SELECT *
  FROM A
 WHERE NOT EXISTS (SELECT *
                     FROM A t
                    WHERE t.id <> a.id
                      AND t.address = a.address
                      AND t.time > a.time)

大数据下可能这个性能好一点，不需要生成序列号临时数据。