查找重复名称sql

Question

SELECT COUNT(organization.ID) 
FROM organization 
WHERE name in ( SELECT name FROM organization GROUP BY name HAVING count( name ) >1 )
AND organization.APPROVED=0 
AND organization.CREATED_AT>'2010-07-30 10:30:21'

我正在尝试查找副本，但是这个查询花费了很长的时间--大约5-6秒。还有其他方法不用我的方法就能找到副本吗？谢谢。

SubQuery: 0.28秒。每件事5.98秒。

Answer 1

SELECT organization.name, COUNT(organization.ID)
  FROM organization
 WHERE organization.APPROVED=0
   AND organization.CREATED_AT>'2010-07-30 10:30:21'
 GROUP BY name
HAVING count(organization.id) > 1;

Answer 2

没有必要在WHERE子句中使用查询。您可以使用GROUP BY和HAVING子句来完成这一任务：

SELECT COUNT(o.ID) 
FROM organization o
WHERE o.APPROVED=0 
   AND o.CREATED_AT>'2010-07-30 10:30:21'
GROUP BY o.Name
HAVING COUNT(o.name) > 1

Answer 3

为什么不像这样做呢：

SELECT COUNT(organization.ID)
FROM organization 
WHERE organization.APPROVED=0 
AND organization.CREATED_AT>'2010-07-30 10:30:21'
GROUP BY organization.name
HAVING count(organization.name) > 1;