对于表中记录查找相同记录，速度非常慢！
我要在表中的10万条记录中查找100多条相同的记录，居然以下的SQL语句执行后要7分钟以上才能查到记录
Select shumu.id,shumu.name,shumu.descn
From shumu o
Where exists
(Select 1 From shumu shumu Where shumu.shuMuId <> o.shuMuId And shumu.name=o.name And shumu.descn=o.descn)
请问大家我写的难道有问题吗？如果有如何解决这个问题
[解决办法]
select id,name,descn from shumu o
where (select count(1) from shumu name=o.name and descn=o.descn)> 1
[解决办法]
Select shumu.id,shumu.name,shumu.descn
From shumu o
Where exists
(Select 1 From shumu Where shuMuId <> o.shuMuId And name=o.name And descn=o.descn)
[解决办法]
Select id,name,descn
From shumu o
Where exists
(Select 1 From shumu Where shuMuId <> o.shuMuId And name=o.name And descn=o.descn)
[解决办法]
或:
select id,name,descn from shumu o
where exists(select count(1) from shumu name=o.name and descn=o.descn having count(1)> 1)
如果是shuMuId,descn,name相同的记录有多个记录时用distinct只取不同的记录有两条的：
select id,name,descn from shumu o
where exists(select count(distinct shuMuId) from shumu name=o.name and descn=o.descn having count(distinct shuMuId)> 1)
[解决办法]
shuMuId <> o.shuMuId
楼主不要用 <> 或!=这样的效率低，会造成全表扫描，不会根据索引扫描
[解决办法]
建索引了吗?
试试
Select shumu.id,shumu.name,shumu.descn From shumu where name+descn in (select name+descn from shumu group by name+descn having count(*)> 2)
[解决办法]
select shuMuId,name,descn
from shumu o
where
(select count(1) from shumu where name=o.name and descn=o.descn)> 1
[解决办法]
建索引了吗?
试试
Select shumu.id,shumu.name,shumu.descn From shumu where name+descn in (select name+descn from shumu group by name+descn having count(*)> 1)
[解决办法]
用这个：
select id,name,descn
from shumu o
join
(select descn,name
from shumu group by descn,name having count(1)> 1
)a
on
o.name=a.name and o.descn=a.descn
[解决办法]
select id,name,descn from shumuA
where (select count(1) from shumuB WHERE name=shumuA.name and descn=shumuA.descn)> 1
[解决办法]
select name,descn,count(1) from shumuA group by name,descn having(count(1)> 0)
[解决办法]
建索引吧
[解决办法]
[size=12px][/size]
[解决办法]
支持搂主，收藏
[解决办法]
没遇到过这种情况.
[解决办法]
....不是吧,一年多的帖子还没结啊
