SQL:从类别中获取产品，但还必须处于另一组类别中 [英] SQL: Get Products from a category but also must be in another set of categories

问题描述

我目前陷入困境.场景是这样的.我有可能与多个类别相关联的产品.数据结构如下所示:

I am currently stuck in a situation. The scenario is this. I have products who may be associated with multiple categories. The data structure is shown below:

Products Table:
product_id  name 
1           Lemon
2           Kiwis
3           Cheese

产品到类别表

product_id   category_id
1            1
1            2
1            3
2            1
2            3
3            2
3            4

类别表(查询中不是必需的，但在此处添加它以帮助可视化正在发生的事情)

Category Table (not required in query however adding it here to help visualize what is happening)

category_id  name
1            Fruit
2            Yellow
3            Round
4            Dairy

我在这里遇到的困难是，最初我想获取水果类别(类别ID为1)中的所有产品，但我也想检查水果是否为黄色.请记住，黄色不是唯一的过滤器，有时我会想返回黄色和橙色水果，但是由于奶酪是黄色的，我不能返回它，因为它不是水果.但是，为了使事情变得容易一些，我始终知道我将以水果类别为基础.

What I'm struggling with here is that originally I want to get all products that are in the fruit category (category id 1) but I also want to check if a fruit is yellow. Keep in mind that yellow will not be the only filter, sometimes I will want to return yellow and orange fruit, however since cheese is yellow I can't return it since it isn't a fruit. However to make things a bit easier I always know that I am going to look in the fruit category as a base.

数据库结构不能更改为opencart数据库结构.

The database structure can not change as its an opencart database structure.

这是我的尝试:

SELECT GROUP_CONCAT(DISTINCT p2c2.category_id SEPARATOR ',') as categories 
FROM oc_product_to_category p2c 
LEFT JOIN oc_product p ON (p.product_id = p2c.product_id) 
LEFT JOIN oc_product_to_category p2c2 ON (p.product_id = p2c2.product_id) 
WHERE p2c.category_id IN ('1','2')

除了会返回奶酪的事实以外，这种工作.

This kind of works except for that fact that it will return Cheese.

注意:我使用Group Concat是因为在所有这些操作的最后，我的目标是返回的不是太多符合这些类别的产品，而是基于过滤器，我想从符合此条件的产品中返回另一个类别列表标准.所以:

Notes: I use Group Concat because at the end of all of this my goal is to return not so much the products that match these categories but based on the filters I want to return another list of categories from the products that match this criteria. So:

场景:

获取符合类别标准的产品 这些产品的退货类别.

Get Products that match category criteria Return categories of those products.

任何帮助将不胜感激.

推荐答案

这种类型的问题称为关系划分.

有两种常见的解决方案:

There are two common solutions:

1. 第一个解决方案将匹配的类别组合在一起，并与固定的字符串进行比较:

1. First solution strings together the matching categories and compares to a fixed string:

SELECT p2c.product_id
FROM oc_product_to_category p2c
GROUP BY p2c.product_id
HAVING GROUP_CONCAT(p2c.category_id SEPARATOR ',' ORDER BY p2c.category_id) = '1,2'

第二个解决方案针对每个所需值执行JOIN:

SELECT p.product_id 
FROM oc_product p 
INNER JOIN oc_product_to_category p2c1 
  ON (p.product_id = p2c1.product_id AND p2c1.category_id = 1) 
INNER JOIN oc_product_to_category p2c2 
  ON (p.product_id = p2c2.product_id AND p2c2.category_id = 2) 

我在演示文稿中介绍了这些解决方案 SQL查询模式已优化 .我在测试中发现联接解决方案的性能要好得多.

I cover these solutions in my presentation SQL Query Patterns, Optimized. I found in my tests that the join solution is much better for performance.

@Tom的建议是正确的，这是完整查询中的样子:

@Tom's suggestion is right, here's what that would look like in a complete query:

    SELECT p.product_id, GROUP_CONCAT(p2c3.category_id SEPARATOR ',') AS categories
    FROM oc_product p 
    INNER JOIN oc_product_to_category p2c1 
      ON (p.product_id = p2c1.product_id AND p2c1.category_id = 1) 
    INNER JOIN oc_product_to_category p2c2 
      ON (p.product_id = p2c2.product_id AND p2c2.category_id = 2) 
    INNER JOIN oc_product_to_category p2c3
      ON (p.product_id = p2c3.product_id)
    GROUP BY p.product_id;

@Tom建议的DISTINCT不必要，因为您的p2c表应具有对(product_id，category_id)的唯一约束.

The DISTINCT that @Tom suggests shouldn't be necessary, because your p2c table should have a UNIQUE constraint over (product_id, category_id).

这篇关于SQL:从类别中获取产品，但还必须处于另一组类别中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！