用于查找与一组类别匹配的产品的SQL查询 [英] SQL query to find products matching a set of categories

问题描述

我有3个表：产品，类别和pro_cat_link。可以通过表pro_cat_link将产品链接到一个或多个类别。

I have 3 tables: products, categories and pro_cat_link. A product can be linked to one or many categories through the table pro_cat_link.

我的查询必须回答以下问题：查找与一组类别匹配的所有产品。例如：找到所有黄色和水果和甜味的产品。

My query must answer the following problem: find all products that match a set of categories. Ex: find all products that are "yellow AND fruit AND sweet".

在SO中研究这个问题时，我只能找到我目前正在使用的解决方案：
复杂的SQL查询 - 查找匹配多个的项目不同的外键

When researching this problem in SO I could find only the solution what I'm currently using: Complicated SQL Query--finding items matching multiple different foreign keys

在我的情况下，我的查询如下所示：

In my case, my query looks like this:

SELECT products.id, COUNT(DISTINCT categories.id) as countCat
FROM products
INNER JOIN pro_cat_link ON (pro_cat_link.product_id = products.id)
WHERE pro_cat_link.category_id IN (3,6,8,10)
GROUP BY product.id
ORDER BY product.date DESC
HAVING countCat = 4

换句话说，选择与类别ID之一匹配的所有产品（3,6,8,10），并仅保留恰好有4个类别匹配的产品。

In other words, select all products that match one of category ids (3,6,8,10) and keep only those that have exactly 4 categories matching.

这很好用，但我遇到了性能问题因为COUNT（），GROUP BY，ORDER BY使得正确的索引非常有限。任何人都可以想到一个更好的方法来解决这个问题吗？

This works well, but I'm running into performance issues as the COUNT(), GROUP BY, ORDER BY makes proper indexing very limited. Can anyone think of a better way to solve that problem?

推荐答案

你可以消除分组和计数的性能问题，如果你存储某处的那些信息。您可以在名为 total_categories 的产品中添加一列，告诉您产品参与的类别数量。然后您可以说其中total_categories = 4 。如果产品经常更改其类别，则可能更难以维护，因为您必须不断更新此字段 - 然后您必须决定是否要在应用程序代码或触发器或存储过程中执行此操作...

You could eliminate the performance problems of grouping and counting if you stored that information somewhere. You could add a column to Products called total_categories that will tell you how many categories the product participates in. Then you could just say where total_categories = 4. This might be more difficult to maintain if products are often changing their categories because you'd have to constantly update this field correctly - and then you have to decide if you want to do that in application code or in a trigger or in a stored procedure...

通常我不认为将这些元数据直接存储在表中是一个好主意，但如果性能真的这很糟糕，值得考虑。

Normally I would not think it a very good idea to store such metadata directly in a table, but if the performance is really that bad, it might be worth considering.

这篇关于用于查找与一组类别匹配的产品的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！