sql如何查询出指定字段不重复但是要保留最新的一个字段的值
sql如何查询出所有符合“指定字段不重复但是要保留最新的一个字段”他们所有的值的最新记录。 sql
[解决办法]
drop table tb
go
create table tb(v int,vv varchar(10),t datetime)
insert into tb
select 1,'ee','2012-09-09' union all
select 1,'ff','2012-09-11' union all
select 1,'aa','2012-10-09' union all
select 2,'aa','2012-09-09' union all
select 2,'bb','2012-11-18' union all
select 2,'cc','2012-01-21' union all
select 2,'dd','2012-12-09'
select v,vv,t
from
(
select *,
ROW_NUMBER() over(partition by v order by t desc) as rownum
from tb
)t
where rownum = 1
/*
vvvt
1aa2012-10-09 00:00:00.000
2dd2012-12-09 00:00:00.000
*/
select * from tb a
left join (select max(id) as id from tb group by 重复字段...)b
on a.id=b.id