SQL过滤器和规则跨越多个行

问题描述：

item table 
| id | name | 

item_category link table 
| item_id | category_id | 

category table 
| id | name |

如果我要取的是涉及到许多类别的一个项目，我可以简单地做：

SELECT item.* 
FROM item 
JOIN item_category ON item_category.item_id = item.id 
LEFT JOIN category ON category.id = item_category.category_id 
WHERE category.name in ("category_one", "category_two")

但是，如果我想要获得与所有类别列表相关的项目，则问题会变得稍微复杂一些，因为从我的查询返回的行每个都包含一个类别。如何编写一个查询，其中只包含与所有类别相关的项目？

我试着写一个查询与嵌套的选择是这样的：

SELECT item.* 
FROM item 
WHERE EXISTS (
    SELECT item.id 
    FROM item_category ON item_category.item_id = item.id 
    LEFT JOIN category ON category.id = item_category.category_id 
    WHERE item_category.id = item.id 
    AND category.name = "category_one" 
) 
AND EXISTS (
    SELECT item.id 
    FROM item_category ON item_category.item_id = item.id 
    LEFT JOIN category ON category.id = item_category.category_id 
    WHERE item_category.id = item.id 
    AND category.name = "category_two" 
)

但是，这是即使在相关领域的指标令人难以置信unperformant。

谢谢你对这个问题的任何意见。

请参阅：[为什么我应该为我认为是非常简单的SQL查询提供一个MCVE？]（https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an -mcve换什么，似乎对我将要-A-极简单的SQL查询）。此外，关于查询性能的问题总是需要SHOW CREATE TABLE语句来显示所有相关的表格以及EXPLAIN的结果。 – Strawberry

顺便说一句，'LEFT JOIN x ... WHERE x = ...'和'INNER JOIN x ...'一样 – Strawberry

答

执行此操作的典型方法是（1）为每个必须匹配的值加入“类别”电缆一次，或者（2）汇总您的第一个查询（按项目分组）和筛选，其中count(distinct category.name) =您的价值清单中的项目数量。

虽然'ALL'是个特例，不是吗？ – Strawberry

如果他是指存在的所有类别，是的;但我认为它是指列表中的所有类别。 – Uueerdo

我看，是的 - 也许我误解了 – Strawberry

答

考虑以下...

DROP TABLE IF EXISTS category; 

CREATE TABLE category 
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY 
,name VARCHAR(12) NOT NULL UNIQUE 
); 

INSERT INTO category VALUES 
(101,'animals'), 
(102,'minerals'), 
(103,'vegetables'); 

DROP TABLE IF EXISTS item_category; 

CREATE TABLE item_category 
(item_id INT NOT NULL 
,category_id INT NOT NULL 
,PRIMARY KEY(item_id,category_id) 
); 

INSERT INTO item_category VALUES 
(1,101), 
(1,102), 
(1,103), 
(2,102), 
(3,101), 
(3,103);

通过观察，我们可以看到，只有1项与所有类别有关。

那么，我们如何选择那些不是？

SELECT DISTINCT ic.item_id 
      FROM item_category ic 
      JOIN category c ON c.id <> ic.category_id 
      LEFT 
      JOIN item_category x 
      ON x.item_id = ic.item_id 
      AND x.category_id = c.id 
      WHERE x.item_id IS NULL; 
+---------+ 
| item_id | 
+---------+ 
|  2 | 
|  3 | 
+---------+

与所有类别有关的项目列表与此组相反。

SQL过滤器和规则跨越多个行

相关推荐