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

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

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_idcategory_id组合的唯一约束。如果有唯一约束,您可以从HAVING子句中删除DISTINCT

英文:

Use aggregation in item_categories to get the item_ids 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.

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:

确定