mysql - 左加入所有结果

Question

大家好，我有一个查询和左连接的问题

我有 2 表产品和特性我知道一些 id 为 1,3,6 的必需特征

 SELECT * FROM products
    LEFT JOIN characteristics ...
    WHERE characteristics_id IN (1,2,6)

但这对我不起作用，因为我需要具有所有这些特性的产品，而不仅仅是一个。

我真的需要

 SELECT * FROM products
    INNER JOIN characteristics as c1 ... AND c1.id=1
    INNER JOIN characteristics as c2 ... AND c2.id=2
    INNER JOIN characteristics as c2 ... AND c2.id=3
...

但它不喜欢我，必须有更简单高效的

谢谢。

Answer 1

首先，您根本不需要LEFT JOIN。

其次，您的第一次尝试似乎完全有效，而且我认为这是最有效的(当要连接的表上有索引时)。但我正在添加另一种方式(这似乎更简单，因为它只有一个连接)。

---

简单(添加超过 3 个特征):

SELECT p.*
FROM products AS p
  INNER JOIN characteristics AS c ON ...
WHERE c.id IN (1, 3, 6)
GROUP BY p.id
HAVING
  COUNT(*) = 3             --- or COUNT(DISTINCT c.id)
                           --- depending on your data

---

更高效(在大多数情况下，换句话说:在具有不同大小和 ID 数量的表中再次测试、测试和测试):

SELECT p.* 
FROM products AS p
  INNER JOIN characteristics AS c1 ON ... 
  INNER JOIN characteristics AS c2 ON ... 
  INNER JOIN characteristics AS c3 ON ... 
WHERE c1.id = 1 
  AND c2.id = 3 
  AND c3.id = 6