我有一个表,有两列,如下所示:
source_cid inchikey
---------- --------
1 qqmn
2 qqmn
3 ccmm
现在我要选择具有相同尺寸的源代码
我的问题是:
SELECT source_cid, count(*) as c
FROM inchikey
GROUP BY inchikey HAVING count(*)>1
此代码将永远运行。如何修改?
最佳答案
首先,正如Anigel所说,我们需要看到您的create语句,您应该使用索引。
其次,查询不显示应显示的所有行。
见:http://www.sqlfiddle.com/#!2/a810d/7
SELECT source_cid, count(*) as c
FROM inchikey
GROUP BY inchikey HAVING count(*)>1;
不幸的是,只输出具有源cids 1的行。
select * from inchikey i,
(
SELECT i2.inchikey, count(i2.source_cid) as c
FROM inchikey i2
GROUP BY i2.inchikey HAVING count(i2.source_cid)>1
) as cd
where cd.inchikey = i.inchikey;
这样,将输出带有1和2的行。
关于mysql - MySQL按计数选择出现,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/17150798/