MySQL：多对多如何获取所有（相关）类别和类别组合查询

Question

表：

id | category   (there is a index on id & category)
-----------
1  |  1
1  |  7
1  |  3

2  |  1
2  |  2
2  |  4

3  |  1
3  |  6
3  |  3

SELECT DISTINCT
    category
FROM 
    many_to_many e1     
WHERE id IN 
    (
    SELECT DISTINCT
        e1.id
    FROM 
        many_to_many e1           
    INNER JOIN
        many_to_many x1 
      ON e1.id=x1.id
    WHERE
        e1.category IN (3)
    )

我喜欢返回：** 6, 1, 7**（我通过上面的查询得到的结果）
在我看来，这个查询没有很好地执行，因为子查询搜索所有 ID，并且这个列表可能很大。

另外，数量并不重要，只要 ID 相关即可。
因此，对于性能而言，如果对要填充的每个唯一类别检查一次 100 个 id 就足够了。

其次，我使用另一个查询（子查询）来获取包含该类别的所有 id：

SELECT DISTINCT
        e1.id
    FROM 
        many_to_many e1           
    INNER JOIN
        many_to_many x1 
      ON e1.id=x1.id
    WHERE
        e1.category IN (3)

 returns: 3 & 1

查询我喜欢的结果的最有效方法是什么。是否有更有效（更好的预成型）的解决方案？
我应该使用一个而不是两个查询吗？

Answer 1

SELECT DISTINCT
        x1.category
    FROM 
        many_to_many e1           
    INNER JOIN
        many_to_many x1 
      ON e1.id = x1.id
    WHERE
        e1.category = 3          --- IN (3) 
      AND
        x1.category <> 3         --- NOT IN (3)

您应该使用 EXPLAIN 检查查询计划。 (category, id) 上的复合索引可能适合此查询。

Answer 2

也许是这个？

SELECT DISTINCT e2.category
FROM many_to_many e1, many_to_many e2
WHERE
    e1.category='3'
    AND e1.id=e2.id

这会使用该表两次，获取包含类别为“3”的 ID 的所有类别。这还会返回类别“3”，如果您愿意，可以将其排除。

Answer 3

首先，您的第二个查询（包含类别 3 的所有 id）不需要连接到 x1 （注意您根本不使用它）：

SELECT DISTINCT
    e1.id
FROM 
    many_to_many e1           
WHERE
    e1.category IN (3)

然后您的第一个查询可以简化：

SELECT DISTINCT
    category
FROM 
    many_to_many e1     
WHERE id IN 
    (
    SELECT DISTINCT
        e1.id
    FROM 
        many_to_many e1           
    WHERE
        e1.category IN (3)
    )

解决方案

<然而，您可以不使用子查询而直接连接，这通常比子查询更有效：

SELECT DISTINCT e2.category 
FROM many_to_many e1 
LEFT JOIN many_to_many e2 ON e1.id=e2.id 
WHERE e1.category IN (3);

这将 many_to_many 连接到 id 上的自身形成类别的所有组合每个id。特别是，同一 id 中出现的所有其他类别 i 都会有一行 (3,i)。

如果您不想包含 3，请将 LEFT JOIN 更改为：

LEFT JOIN many_to_may e2 ON e1.id=e2.id AND e1.category<>e2.category