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屋!