Mysql动态交叉表在一张表上进行透视比较

Question

我有一个多对多表设置。这是我关注的表格的一个例子。

many_to_many_id、foreign_key_id
1, 1
1, 2
1, 3
2, 1
2, 2
3, 1
3, 4

我需要给定 many_to_many_id 1 找到任何其他具有与第一组中存在的匹配外键的 many_to_many_ids 。假设附加到 many_to_many_id 的第一个集合 1, 2, 3 将返回 2 作为 1，并且 2 在该集合内，但不会返回 3，因为 4 不是测试集的一部分。我的老板说我应该使用动态交叉表创建两个表以与联接进行比较。我已经寻找了示例，但它们没有帮助。

Answer 1

您可以通过一些简单的子查询来完成此操作。第一个将确保对于每个 many_to_many_id ，您要查找的集合中至少有一个 foreign_key_id (1, 2, 3)，第二个将确保您要查找的集合中没有甚至没有一个foreign_key_id。

SET @search_id = 1;

SELECT m.many_to_many_id FROM SampleTable m
WHERE m.many_to_many_id != @search_id
AND     EXISTS ( SELECT 1 FROM SampleTable a WHERE a.many_to_many_id = m.many_to_many_id AND a.foreign_key_id IN ( SELECT b.foreign_key_id FROM SampleTable b WHERE b.many_to_many_id = @search_id ) )
AND NOT EXISTS ( SELECT 1 FROM SampleTable a WHERE a.many_to_many_id = m.many_to_many_id AND a.foreign_key_id NOT IN ( SELECT b.foreign_key_id FROM SampleTable b WHERE b.many_to_many_id = @search_id ) )
GROUP BY m.many_to_many_id

SQL Fiddle Here