分组取最新记录的SQL如何写？实在不好意思了，没分了。
sbid revtime interfacetype value
08312 2007-8-11 11:00:00 7 12.90
08312 2007-8-11 12:00:00 7 12.21
08312 2007-8-11 12:00:00 10 112.90
如上记录，需要按interfacetype分组，然后取出时间最新的每个interfacetype的记录，就上面的记录而言，结果应该是最后两条记录合法。该怎么写？
[解决办法]
sbid revtime interfacetype value
08312 2007-8-11 11:00:00 7 12.90
08312 2007-8-11 12:00:00 7 12.21
08312 2007-8-11 12:00:00 10 112.90
如上记录，需要按interfacetype分组，然后取出时间最新的每个interfacetype的记录，就上面的记录而言，结果应该是最后两条记录合法。该怎么写？
-----------
select * from t a
where not exists(select 1 from t where interfacetype=a.interfacetype and revtime <a.revtime)
[解决办法]
declare @test table (sbid varchar(10), revtime datetime, interfacetype int, value money)
insert @test
select '08312 ', '2007-8-11 11:00:00 ', '7 ', 12.90 union all
select '08312 ', '2007-8-11 12:00:00 ', '7 ', 12.21 union all
select '08312 ', '2007-8-11 12:00:00 ', '10 ', 112.90
select * from @Test a where revtime in (select top 1 revtime from @Test where interfacetype = a.interfacetype order by revtime desc)
[解决办法]
create table t
(sbid varchar(10), revtime datetime, interfacetype int, value numeric(12,2))
insert into t
select '08312 ', '2007-8-11 11:00:00 ', 7 , 12.90 union all
select '08312 ', '2007-8-11 12:00:00 ', 7 , 12.21 union all
select '08312 ', '2007-8-11 12:00:00 ', 10, 112.90
select * from t a
where not exists(select 1 from t where interfacetype=a.interfacetype and revtime > a.revtime)
sbid revtime interfacetype value
---------- ------------------------------------------------------ ------------- --------------
08312 2007-08-11 12:00:00.000 7 12.21
08312 2007-08-11 12:00:00.000 10 112.90
(2 row(s) affected)
[解决办法]
select *
from @Test a
where revtime =
(select max(revtime) from @Test where interfacetype =
a.interfacetype)
[解决办法]
create table t
(sbid varchar(10), revtime datetime, interfacetype int, value numeric(12,2))
insert into t
select '08312 ', '2007-8-11 11:00:00 ', 7 , 12.90 union all
select '08312 ', '2007-8-11 12:00:00 ', 7 , 12.21 union all
select '08312 ', '2007-8-11 12:00:00 ', 10, 112.90
select * from t a
where not exists(select 1 from t where interfacetype=a.interfacetype and revtime > a.revtime)
