求sql大神帮忙，急急。。。

[xiaoyaodeyun2008]

有姓名和年龄两个字段，姓名有重复，要求查出所有的姓名和年龄，姓名重复的只查出年龄最大的那一个。再一个，查出重复次数最多的姓名。

[geniuswjt]

1.查出所有的姓名和年龄，姓名重复的只查出年龄最大的那一个
select * from (
select rid=row_number() over (partition by 姓名 order by 年龄 desc),* from tb
) t
where rid=1

2.查出重复次数最多的姓名
select 姓名,count(1) from tb 
group by 姓名 having count(1)>1
order by 2 desc

[--小F--]

select * from tb t where age=(select max(age) from tb where name=t.name)

select top 1 name from tb where name in (select 姓名 from tb group by 姓名  having count(1)>1) 

[xiaoyaodeyun2008]

[Quote=引用 1 楼 geniuswjt 的回复:]

SQL code

1.查出所有的姓名和年龄，姓名重复的只查出年龄最大的那一个
select * from (
select rid=row_number() over (partition by 姓名 order by 年龄 desc),* from tb
) t
where rid=1

2.查出重复次数最多的姓名
select 姓名,count(1) from tb
group by ……
[/Quote]谢谢，如果第二个是查出重复最多的前三条叻？

[--小F--]

select top 1 姓名,count(1) from tb 
group by 姓名 having count(1)>1
order by 2 desc

[--小F--]

select top 3 姓名,count(1) from tb 
group by 姓名 having count(1)>1
order by 2 desc

[geniuswjt]

select top 3 姓名,count(1) from tb 
group by 姓名 having count(1)>1
order by 2 desc

[Quote=引用 3 楼 xiaoyaodeyun2008 的回复:]
引用 1 楼 geniuswjt 的回复:

SQL code

1.查出所有的姓名和年龄，姓名重复的只查出年龄最大的那一个
select * from (
select rid=row_number() over (partition by 姓名 order by 年龄 desc),* from tb
) t
where rid=1

2.查出重复次数最多的姓名
selec……
[/Quote]

[@信心]

//姓名重复的只查出年龄最大的那一个
select distinct (name)) from tb yy
where age= (select max(age) from tb xx where xx.name=yy.name )
;

[@信心]

//查出重复次数最多的姓名

select name,max(agetimes )from
(select distinct name ,count(age) as agetimes from tb
group by name
order by desc
) a