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)
建索引了吗?试试 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)
roy_88(中国风_燃烧你的激情!!!) ,首先很感谢你的及时回复,不过你的语句是否少了“where”该是笔误漏了吧,应该是: select id,name,descn from shumu o where (select count(1) from shumu where name=o.name and descn=o.descn)>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
From shumu o
Where exists
(Select 1 From shumu Where shuMuId<>o.shuMuId And name=o.name And descn=o.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)
楼主不要用<>或!=这样的效率低,会造成全表扫描,不会根据索引扫描
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)
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
where (select count(1) from shumu where name=o.name and descn=o.descn)>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
采用你上面的方法确实时间缩短很多。我还有一个问题:
如果有同样一张表名称和原来的表名称一样,只不过把一张表作为临时表用。现如果要把临时表中的记录和另一张表有100多万条记录去配比查询是否有和临时表一样的重复记录,又该怎么做呢?
where (select count(1) from shumuB WHERE name=shumuA.name and descn=shumuA.descn)>1