关于查询重复数据的效率问题
有张表IDManager
字段: ID IDName UserName UserPwd
表有5000多万条数据
我想实现查询各个IDname下的重复数据的条数(只要UserName重复就算重复数据),自己弄了个效率不高,查询要1-3分钟 ,应用到实际中应用程序等不到响应时间:
select count(*) from IDManager where UserName in (select UserName from IDManager group by UserName having count(UserName) > 1) and Id not in (select min(Id) from IDManager group by UserName having count(UserName)>1) and IDName='socity'
该如何提高查询效率?
------解决方案--------------------试试这个:
select count(*)
from idmanager
where username in (
select username
from idmanager
group by username
having count(1)>1)