MySQL按计数选择出现次数

Question

我有一个包含两列的表格，如下所示：

source_cid     inchikey
----------     --------
 1             qqmn
 2             qqmn
 3             ccmm

现在我想选择具有相同inchikeys的source_cids

以下是我的查询：

SELECT source_cid, count(*) as c
FROM inchikey
GROUP BY inchikey HAVING count(*)>1

这段代码会一直运行下去。我该如何修改它？

- user2171526

---

你的表中有多少条记录？这张表只有两列吗，还是这只是为了问题简化了模式？ - user359040

这是一个非常好的问题。另外，“永远”代表什么？1-2分钟？你有多少条记录？你的真实表是什么？还是一个视图？ - Menelaos

我的表格有4列和31451649行。这个查询在我的Python shell中运行了两天。 - user2171526

请显示 CREATE TABLE tablename。你可能需要首先添加索引。 - Anigel

我已经为所有列添加了哈希索引。 - user2171526

我的评论仍然有效，如果您展示了创建表的代码，我们可以复制它并调试为什么它需要这么长时间，并建议如何加速它。 - Anigel

Answer 1

尝试在inchikey表上创建(source_cid, inchikey)的重复索引，然后尝试运行查询：

SELECT inchikey, group_concat(distinct source_cid) source_cids, count(*) as c
FROM inchikey
GROUP BY inchikey HAVING count(distinct source_cid)>1

（您现有的查询仅会显示每个重复inchikey的一个source_cid。）

- user359040

Answer 2

首先，正如Anigel所说，我们需要看到您的创建语句，并且您应该使用索引。

其次，您的查询未显示应显示的所有行。

请参见：http://www.sqlfiddle.com/#!2/a810d/7。

SELECT source_cid, count(*) as c
FROM inchikey
GROUP BY inchikey HAVING count(*)>1;

很遗憾，只有源代码为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。

- Menelaos