mysql - SQL连接两个表并根据另一列及其累积计数映射公共(public)值

Question

我有下面两个表，

表 1:

id    document
--------------
    A2      B200
    A2      B6
    A2      B2
    A2      B3
    A3      B2
    A3      B400
    A5      B100
    A5      B500 
    A6      B6
    A7      B200
    A8      B6
    A8      B2
    A8      B3    
    A8      C1

表 2:

id    name
--------------
A1      Jack
A2      Martin
A3      Jack
A4      Thomas
A5      Jack
A6      Thomas
A7      Thomas
A8      John
A9      John
A10     Kate

我的过滤器如下，它应该比较 document 列并根据此列表进行过滤:

WHERE table1.document IN (B2,B400,B100,B500,B200,B6,B2,B3)

结果应该是这样的:

name1   name2   freq
--------------------
Jack    John    1
Martin  Jack    1
Martin  Thomas  2
Martin  John    3
Thomas  John    1

一些解释:

我们需要构建一个结果表来映射具有共同文档的name及其出现频率。首先，我们需要使用 WHERE IN 语句过滤文档列表，以获取我们要映射的文档列表。

然后我们得到计数大于 1 的文档列表，因为该文档在至少两个 id 之间共享。

然后我们在table2中查找这些id的name并将它们放入结果表中，以及它们共有的文档数。有些名称有多个ID，所以如果我们找到了这些，那么我们就会添加到计数中。

例如，文档B6被分配给A2和A6的id，所以他们有这个文档的共同点，我们在结果表中创建一个条目，将它们相应的名称作为 name1 和 name2(顺序无关紧要)并为其指定频率 1。但是如果我们进一步观察，我们会看到文档 B200 由 A2 和 A7 共享，当我们查找这两个 id 的名称时，我们看到我们已经有一个具有这些名称的条目，因为他们与前一个中的 Martin 和 Thomas 相对应，所以我们加上他们的数量，所以它变成了 2。

另一个例子是 A2 和 A8，Martin 和 John 共享的文档 B6，B2，B3，所以我们为这两个创建一个条目，并且计数将是 3.

这是一个demo数据。

Answer 1

您必须执行两次连接，然后按两个名称分组:

SELECT t2a.name, t2b.name, COUNT(*)
FROM Table1 t1a
INNER JOIN Table2 t2a ON t2a.id = t1a.id
INNER JOIN Table1 t1b ON t1b.document = t1a.document
INNER JOIN Table2 t2b ON t2b.id = t1b.id AND t2a.name < t2b.name
GROUP BY t2a.name, t2b.name