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
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
from tb
group by name,age
having count(*)>1
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)
1楼的大哥,语法错误,group by 没有包含id
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
(
select t.* ,(count(t.id) over ( partition by t.name,t.age)) as num from tal t
)
where num>1
from (select t.*, count(t.id) over(partition by t.name, t.age) as num
from tbl t) a
where num > 1;这样效率应该也不错。