mysql - 需要一个 MySQL JOIN 来返回产品所属的所有类别，每个产品，即使只搜索一个类别

Question

我定义了下表:

CREATE TABLE products (
    product_id INTEGER(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    section VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (product_id)
) ENGINE=MyISAM;

CREATE TABLE categories (
    category_id INTEGER(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (category_id)
) ENGINE=MyISAM;

CREATE TABLE product_categories (
    product_id INTEGER(11) NOT NULL,
    category_id INTEGER(11) NOT NULL,
    PRIMARY KEY (product_id, category_id)
) ENGINE=MyISAM;

实际上还有更多，这是优化更大、更复杂查询的一部分。其中一部分是将一些缓慢的子查询移到 View 中，到目前为止这很有帮助。

当我添加 categories/product_categories 表并在允许用户按 products.section 或 categories.category_id 搜索时加入它们时，查询变得非常慢。 UI 将这些作为搜索参数传递进来，我试图为每个产品获取一行，其中包含其 ID、名称、部分以及与其关联的以逗号分隔的类别名称列表。我能够通过以下 View 和查询更快地完成此操作:

CREATE OR REPLACE
ALGORITHM = MERGE
VIEW view_products_with_categories
AS
SELECT 
    p.product_id,
    p.name,
    p.section,
    pc.name AS category
products p
LEFT JOIN product_categories pc on p.product_id = pc.product_id
LEFT JOIN categories c ON pc.category_id = c.category_id;

SELECT
    product_id,
    name,
    section,
    GROUP_CONCAT(DISTINCT category ORDER BY category) AS categories
FROM view_products_with_categories
GROUP BY product_id;

假设我们有以下行:

product_id  name               section          category_id category
332913      Model Train Engine child-and-baby   1160        child-and-baby>baby-and-pre-schooltoys>playsets
332913      Model Train Engine child-and-baby   1308        toys>baby-and-preschool>playsets
332913      Model Train Engine child-and-baby   1312        toys>carstrains-and-planes>cars-and-vehicles

上面的简单查询给了我以下信息:

product_id  name                section         categories
332913      Model Train Engine  child-and-baby  child-and-baby>baby-and-pre-schooltoys>playsets,toys>baby-and-preschool>playsets,toys>carstrains-and-planes>cars-and-vehicles

很好，正如预期的那样。但是，我希望用户能够按 category_id 进行搜索。目前，我们的 UI 对类别名称做了一些自动完成魔术，并向动态生成的 SQL 添加了一个过滤器，其中包含 category_id。如果我在 GROUP_CONCAT 查询中保留了 category_id，它会是 1160。假设他们想搜索第二个 (1308)，所以我们修改查询如下:

SELECT
    product_id,
    name,
    section,
    GROUP_CONCAT(DISTINCT category ORDER BY category) AS categories
FROM view_products_with_categories
WHERE category_id = 1308
GROUP BY product_id;

现在我们得到以下内容:

product_id  name                section         categories
332913      Model Train Engine  child-and-baby  toys>baby-and-preschool>playsets

同样，这正是您所期望的。但是，客户希望查看与具有他们正在搜索的一个或多个类别的产品关联的所有类别。因此，让我们制作一些简化的示例数据来向您展示我正在寻找的内容:

product_id  name       section     category_id  category
1           product_1  section_1   1            category_1
1           product_1  section_1   2            category_2 
1           product_1  section_1   3            category_3
2           product_2  section_2   3            category_3
2           product_2  section_2   4            category_4 
2           product_2  section_2   5            category_5

如果用户搜索 category_id = 3，我希望他们得到以下信息:

product_id  name      section   categories
1           product_1 section_1 category_1, category_2, category_3 
2           product_2 section_2 category_3, category_4, category_5 

但我目前只得到:

product_id  name      section   categories
1           product_1 section_1 category_3 
2           product_2 section_2 category_3

我只是想不出没有子查询的方法，它的缓慢是我首先转向 View 的原因。我希望我遗漏了一些非常明显的东西，可能是由于 sleep 不足，所以我们将不胜感激。

更新:我还应该提到，产品可能不属于任何类别，因此我的代码中有 LEFT JOIN。

Answer 1

以下查询有效:(虽然我没有使用 View )

select pc1.product_id, products.name, products.section,  
  group_concat(categories.name)
from products
inner join product_categories pc1 
on (pc1.product_id =    products.product_id and pc1.category_id =  3)
inner join product_categories pc2 
on (pc2.product_id = products.product_id)
inner join categories  
on (categories.category_id = pc2.category_id)
group by pc1.product_id, products.name, products.section

如果你想使用 View ，以下将起作用:

SELECT  vpc.product_id,vpc.name,vpc.section,
GROUP_CONCAT(DISTINCT category ORDER BY category) AS categories
FROM view_products_with_categories vpc
inner join product_categories pc 
on (pc.product_id = vpc.product_id and pc.category_id=3) 
GROUP BY vpc.product_id, vpc.name, vpc.section;