一般ID是不变的: select * from tb a where not exists(select 1 from tb where name=a.name and age>a.age)
select * from tb a where not exists(select * from tb where name=a.name and age>a.age)
select id=row_number()over(order by getdate()),name,age from tb t where age=(select max(age) from tb where name=t.name)
显示只有一条数据的不要. fredrickhu大哥错啦
select c.id,b.name,b.age from (select COUNT(id) id,name name from tb1 group by name having count(id)>1)a join (select MAX(age) age,name from tb1 group by name) b on a.name=b.name join tb1 c on c.name=b.name and c.age=b.age
这句绝对可以select ROW_NUMBER()over(order by bb.name) as id,bb.name,max(bb.age) as age from ( select * from tt where name in( select name from tt group by name having count(name)>1 ) ) bb group by bb.name
select * from tb a where not exists(select 1 from tb where name=a.name and age>a.age)
id=row_number()over(order by getdate()),name,age
from
tb t
where
age=(select max(age) from tb where name=t.name)
fredrickhu大哥错啦
select
c.id,b.name,b.age
from
(select COUNT(id) id,name name from tb1 group by name having count(id)>1)a
join
(select MAX(age) age,name from tb1 group by name) b
on a.name=b.name
join tb1 c
on c.name=b.name and c.age=b.age
from
(
select * from tt where name in(
select name from tt
group by name
having count(name)>1 )
) bb
group by bb.name