英文:
SQL query for items joined to multiple categories
问题
我有这两张表,内容项和一个连接到类别的连接表。我只需要一个查询来选择在多个类别中的项目(在所有类别中,而不是在任何类别中)。
-- 内容项
CREATE TABLE `items` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
[...]
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- 连接到类别表的连接表
CREATE TABLE `item_categories` (
`item_id` bigint(20) DEFAULT NULL,
`category_id` bigint(20) DEFAULT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
以下是选择在两个类别中的连接表条目的查询:
SELECT `item_categories`.* FROM `item_categories`
WHERE `item_categories`.`category_id` IN (11811, 11911)
现在将其与项目表连接,以获取在任何类别中的项目。简单:
SELECT DISTINCT `items`.* FROM `items`
INNER JOIN `item_categories` ON `item_categories`.`item_id` = `items`.`id`
WHERE `item_categories`.`category_id` IN (11811, 11911)
这有点儿愚蠢,是的,但尝试使用 AND 的查询(每次都会返回 0 个结果):
SELECT DISTINCT `items`.* FROM `items`
INNER JOIN `item_categories` ON `item_categories`.`item_id` = `items`.`id`
WHERE `item_categories`.`category_id` = 11811
AND `item_categories`.`category_id` = 11911
这是我能想到的最接近的方法。它似乎过于复杂,一种手动连接的方法,对于每个类别都有一个 WHERE ID IN
:
SELECT DISTINCT `items`.* FROM `items`
WHERE ID IN (
SELECT `item_categories`.item_id FROM `item_categories`
WHERE `item_categories`.`category_id` = 11811
)
AND ID IN (
SELECT `item_categories`.item_id FROM `item_categories`
WHERE `item_categories`.`category_id` = 11911
)
有没有更好(更简单)的方法来实现这个目标?
我想必须有一种方法可以在连接中放置条件,使用自连接或分组来选择仅在所有类别中的项目类别。
英文:
I've got these 2 tables, content item and a join table to categories. I simply need a query to select items that are in multiple categories (in ALL categories, not in any category).
-- content items
CREATE TABLE `items` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
[...]
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- a join table to a categories table
CREATE TABLE `item_categories` (
`item_id` bigint(20) DEFAULT NULL,
`category_id` bigint(20) DEFAULT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Here is a query to select entries in the join table in both categories:
SELECT `item_categories`.* FROM `item_categories`
WHERE `item_categories`.`category_id` IN (11811, 11911)
Now joining that with the items table gives me items in ANY category. Simple:
SELECT DISTINCT `items`.* FROM `items`
INNER JOIN `item_categories` ON `item_categories`.`item_id` = `items`.`id`
WHERE `item_categories`.`category_id` IN (11811, 11911)
This is a little silly, yes, but an attempt with an AND (0 results every time):
SELECT DISTINCT `items`.* FROM `items`
INNER JOIN `item_categories` ON `item_categories`.`item_id` = `items`.`id`
WHERE `item_categories`.`category_id` = 11811
AND `item_categories`.`category_id` = 11911
This is the closest I've gotten. It seems overly complicated, a sort of manual join with a WHERE ID IN
for each category:
SELECT DISTINCT `items`.* FROM `items`
WHERE ID IN (
SELECT `item_categories`.item_id FROM `item_categories`
WHERE `item_categories`.`category_id` = 11811
)
AND ID IN (
SELECT `item_categories`.item_id FROM `item_categories`
WHERE `item_categories`.`category_id` = 11911
)
Is there a better (simpler) way to achieve this?
I'm thinking there must be a way to put a condition on the join with a self join or group by to select only item_categories in all categories.
答案1
得分: -1
使用聚合在item_categories
中获取属于所有类别的item_id
:
SELECT *
FROM items
WHERE id IN (
SELECT item_id
FROM item_categories
WHERE category_id IN (11811, 11911)
GROUP BY item_id
HAVING COUNT(DISTINCT category_id) = 2 -- 类别的数量
);
我在item_categories
的定义中没有看到item_id
和category_id
组合的唯一约束。如果有唯一约束,您可以从HAVING
子句中删除DISTINCT
。
英文:
Use aggregation in item_categories
to get the item_id
s that belong to all the categories:
SELECT *
FROM items
WHERE id IN (
SELECT item_id
FROM item_categories
WHERE category_id IN (11811, 11911)
GROUP BY item_id
HAVING COUNT(DISTINCT category_id) = 2 -- the number of categories
);
I don't see in the definition of item_categories
any unique constraint for the combination of item_id
and category_id
.<br/>
If there is one, you may remove DISTINCT
from the HAVING
clause.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论