MySQL复杂的关系SELECT [英] MySQL complicated relational SELECT

问题描述

我有一个 categories 表，其中包含 id、parent 和 name 字段.parent 字段允许一个类别成为另一个类别的子类别.

I have a categories table with id, parent and name fields. The parent field allows a category to be a subcategory of another category.

示例 categories 表，其中有两个主要类别(WIDGETS 和 THINGAMABOBS)，并且 WIDGETS 有 3 个子类别:

Example categories table where there are two main categories (WIDGETS and THINGAMABOBS), and WIDGETS have 3 subcategories:

• id 1, parent null, name "WIDGETS"
• id 2、父 1、名称小工具"
• id 3, parent 1, name "DOOHICKEYS"
• id 4, parent 1, name "GIZMOS"
• id 5, parent null, name "THINGAMABOBS

• id 1, parent null, name "WIDGETS"
• id 2, parent 1, name "GADGETS"
• id 3, parent 1, name "DOOHICKEYS"
• id 4, parent 1, name "GIZMOS"
• id 5, parent null, name "THINGAMABOBS

我有一个带有 category 字段的 products 表

I have a products table with category field

示例产品记录，其中产品链接到GIZMOS"类别:

Example products record where product is linked to the "GIZMOS" category:

• id 1, category 4, name Contraption 5000

• id 1, category 4, name Contraption 5000

我希望能够在 SELECT 语句中提供类别名称并取回属于该类别的所有产品.但我不仅要在GIZMOS"上找到上述记录，而且我还希望能够通过名称WIDGET"找到它，因为 MEDIUM WIDGET 是 WIDGET 的子项.这应该适用于无限数量的级别(即:子子子类别)

I want to be able to supply a category name in a SELECT statement and get back all products that are in that category. But not only do I want to find the above record on "GIZMOS", but I also want to be able to find it by the name "WIDGET", because MEDIUM WIDGET is a child of WIDGET. This should work across an unlimted number of levels (ie: sub-sub-sub categories)

为了让这更加复杂，我希望能够将产品分配给多个类别.也许他们会用逗号分隔?即:如果我希望 Contraption 5000 存在于 Doohickeys 和 Thingamabobs 类别中，我会将 3,5 放在 category 字段中.

To make this even more complicated, I want to be able to assign a product to more than one category. Perhaps they would be separated by commas? i.e.: If I wanted the Contraption 5000 to exist in the Doohickeys and Thingamabobs categories, I would put 3,5 in the category field.

我要问的问题是否可以通过单个 select 语句实现?

Is what I'm asking possible with a single select statement?

推荐答案

在您的应用程序代码中执行此操作！维护起来会更简单、更容易.

Do this in your application code! It will be much simpler and easier to maintain.

另见这篇类似的帖子(实际上是关于这个话题有很多帖子)

这篇关于MySQL复杂的关系SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！