SQL查询用于与多个类别关联的项目

huangapple go评论81阅读模式
英文:

SQL query for items joined to multiple categories

问题

我有这两张表,内容项和一个连接到类别的连接表。我只需要一个查询来选择在多个类别中的项目(在所有类别中,而不是在任何类别中)。

  1. -- 内容项
  2. CREATE TABLE `items` (
  3. `id` bigint(20) NOT NULL AUTO_INCREMENT,
  4. [...]
  5. PRIMARY KEY (`id`)
  6. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  7. -- 连接到类别表的连接表
  8. CREATE TABLE `item_categories` (
  9. `item_id` bigint(20) DEFAULT NULL,
  10. `category_id` bigint(20) DEFAULT NULL,
  11. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

以下是选择在两个类别中的连接表条目的查询:

  1. SELECT `item_categories`.* FROM `item_categories`
  2. WHERE `item_categories`.`category_id` IN (11811, 11911)

现在将其与项目表连接,以获取在任何类别中的项目。简单:

  1. SELECT DISTINCT `items`.* FROM `items`
  2. INNER JOIN `item_categories` ON `item_categories`.`item_id` = `items`.`id`
  3. WHERE `item_categories`.`category_id` IN (11811, 11911)

这有点儿愚蠢,是的,但尝试使用 AND 的查询(每次都会返回 0 个结果):

  1. SELECT DISTINCT `items`.* FROM `items`
  2. INNER JOIN `item_categories` ON `item_categories`.`item_id` = `items`.`id`
  3. WHERE `item_categories`.`category_id` = 11811
  4. AND `item_categories`.`category_id` = 11911

这是我能想到的最接近的方法。它似乎过于复杂,一种手动连接的方法,对于每个类别都有一个 WHERE ID IN

  1. SELECT DISTINCT `items`.* FROM `items`
  2. WHERE ID IN (
  3. SELECT `item_categories`.item_id FROM `item_categories`
  4. WHERE `item_categories`.`category_id` = 11811
  5. )
  6. AND ID IN (
  7. SELECT `item_categories`.item_id FROM `item_categories`
  8. WHERE `item_categories`.`category_id` = 11911
  9. )

有没有更好(更简单)的方法来实现这个目标?

我想必须有一种方法可以在连接中放置条件,使用自连接或分组来选择仅在所有类别中的项目类别。

英文:

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).

  1. -- content items
  2. CREATE TABLE `items` (
  3. `id` bigint(20) NOT NULL AUTO_INCREMENT,
  4. [...]
  5. PRIMARY KEY (`id`)
  6. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  7. -- a join table to a categories table
  8. CREATE TABLE `item_categories` (
  9. `item_id` bigint(20) DEFAULT NULL,
  10. `category_id` bigint(20) DEFAULT NULL,
  11. ) 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:

  1. SELECT `item_categories`.* FROM `item_categories`
  2. WHERE `item_categories`.`category_id` IN (11811, 11911)

Now joining that with the items table gives me items in ANY category. Simple:

  1. SELECT DISTINCT `items`.* FROM `items`
  2. INNER JOIN `item_categories` ON `item_categories`.`item_id` = `items`.`id`
  3. WHERE `item_categories`.`category_id` IN (11811, 11911)

This is a little silly, yes, but an attempt with an AND (0 results every time):

  1. SELECT DISTINCT `items`.* FROM `items`
  2. INNER JOIN `item_categories` ON `item_categories`.`item_id` = `items`.`id`
  3. WHERE `item_categories`.`category_id` = 11811
  4. 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:

  1. SELECT DISTINCT `items`.* FROM `items`
  2. WHERE ID IN (
  3. SELECT `item_categories`.item_id FROM `item_categories`
  4. WHERE `item_categories`.`category_id` = 11811
  5. )
  6. AND ID IN (
  7. SELECT `item_categories`.item_id FROM `item_categories`
  8. WHERE `item_categories`.`category_id` = 11911
  9. )

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

  1. SELECT *
  2. FROM items
  3. WHERE id IN (
  4. SELECT item_id
  5. FROM item_categories
  6. WHERE category_id IN (11811, 11911)
  7. GROUP BY item_id
  8. HAVING COUNT(DISTINCT category_id) = 2 -- 类别的数量
  9. );

我在item_categories的定义中没有看到item_idcategory_id组合的唯一约束。如果有唯一约束,您可以从HAVING子句中删除DISTINCT

英文:

Use aggregation in item_categories to get the item_ids that belong to all the categories:

  1. SELECT *
  2. FROM items
  3. WHERE id IN (
  4. SELECT item_id
  5. FROM item_categories
  6. WHERE category_id IN (11811, 11911)
  7. GROUP BY item_id
  8. HAVING COUNT(DISTINCT category_id) = 2 -- the number of categories
  9. );

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.

huangapple
  • 本文由 发表于 2023年7月13日 13:54:44
  • 转载请务必保留本文链接:https://go.coder-hub.com/76676307.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定