mysql - 从数据库中获取交叉记录

Question

我有以下表结构(简化):

id | structure_id | structure_hash_id
1    1              1
2    1              2
3    1              3
4    2              4
5    2              1
6    3              2

如您所见，所有结构都包含许多结构哈希。我想要获取的是每个结构 id 的信息，它包含多少结构散列存在于其他结构中。所以对于这个例子，它将是:

structure_id #1: 2
structure_id #2: 1
structure_id #3: 1

我为此写的查询是:

SELECT contains.structure_id, COUNT(contains.structure_hash_id)
FROM (
   SELECT *
   FROM structureTable st
   WHERE structure_id = 1
) AS contains
INNER JOIN (
   SELECT *
   FROM structureTable st
   WHERE structure_id != 1
) AS notcontains
ON contains.structure_hash_id = notcontains.structure_hash_id
GROUP BY contains.structure_id;

它有效，我凭内存写的，我不记得我之前是怎么写的，因为我删除了它，但你明白了。

但问题是，在实际表中我有大约 5 亿条记录和一些其他列，因此对于每个 structure_id，查询执行时间很长(> 15 分钟)。

此外，我手动输入了 structure_id，但我希望将它们全部作为结果，就像我在本文顶部给出的示例一样。

我该如何解决这个问题？

Answer 1

您可以通过自连接和分组来实现这一点。

方法如下:

select  
t1.structure_id ,
count(t1.structure_id ) as count

from  structure  t1 
inner join structure  t2 on t1.structure_id !=t2.structure_id  
and t1.structure_hash_id=t2.structure_hash_id
group by t1.structure_id 

SQL Fiddle 示例:http://sqlfiddle.com/#!9/678bf7/1/0