求一条高效的sql语句

[ojuju10]

create table tbl(id int ,name varchar(10),age int);
insert into tbl(id,name,age) select 1,'aa',15 from dual;
insert into tbl(id,name,age) select 2,'aa',15 from dual;
insert into tbl(id,name,age) select 3,'bb',15 from dual;
insert into tbl(id,name,age) select 4,'bb',15 from dual;
insert into tbl(id,name,age) select 5,'bb',15 from dual;
insert into tbl(id,name,age) select 6,'dd',15 from dual;
insert into tbl(id,name,age) select 7,'ee',15 from dual;
insert into tbl(id,name,age) select 8,'ff',15 from dual;

要求tbl中name和age记录重复时，查询出重复的记录数。因为数据量比较大，最好用条高效的sql语句实现

最后的结果为：

id name age
1 aa 15
2 aa 15
3 bb 15
4 bb 15
5 bb 15

[Andy__Huang]

select *
from tb
group by name,age
having count(*)>1

[Andy__Huang]

修改:
select *
from tb a
where exists( select 1
from (select name,age,count(*) num from tb group by name,age having count(*)>1 )b
where a.name=b.name and a.age=b.age)

[ojuju10]

1楼的大哥，语法错误，group by 没有包含id

[代码摆渡人]

select name, age, count(1) from tbl group by name, age having count(1) > 1;

[代码摆渡人]

不要用“*”, 用１的效率高。

[代码摆渡人]

[Quote=引用 2 楼 hdhai9451 的回复:]
修改:
select *
from tb a
where exists( select 1
from (select name,age,count(*) num from tb group by name,age having count(*)>1 )b
where a.name=b.name and a.age=b.age)
[/Quote]

为什么要再做一个联合？
不明白，这样效率会更高？

[merrill]

select
a.id,a.name,a.age
from
(select t.id,t.name,t.age,row_number() over(partition by t.name,t.age order by t.id) num from tb1 t)a
where a.num>1

[wqyitian]

select * from
(
select t.* ,(count(t.id) over ( partition by t.name,t.age)) as num from tal t
)
where num>1

[代码摆渡人]

select distinct a.name, a.age, a.num
from (select t.*, count(t.id) over(partition by t.name, t.age) as num
from tbl t) a
where num > 1;

这样效率应该也不错。

[hebo2005]

[Quote=引用 5 楼 huang_xw 的回复:]
不要用“*”, 用１的效率高。
[/Quote]
这个没有确切的定论，基本上两者是一样的，count(*)会走索引列的，如果是count具体字段，倒是可能会慢的