SQL去重复记录,留一条符合条件记录 宁静以致远

declare @tbl table(name varchar(20),age datetime)
insert into @tbl 
select 'jacky','1985-12-23' union 
select 'myjacky','1986-12-10' union
select 'myjacky','1986-12-12' union
select 'jacky','1988-09-23'

--方法一
select a.* from @tbl a 
inner join (select max(age) as age,name from @tbl group by name) b
on a.name=b.name and a.age=b.age

--方法二
select a.* from @tbl a where 1>(select count(*) from @tbl where name = a.name and age > a.age) order by a.name

--方法三
select a.* from @tbl a where not exists(select 1 from @tbl where name = a.name and age < a.age)

--方法四
select a.* from @tbl a where age = (select min(age) from @tbl where name = a.name) order by a.name