mySQL查询选择子项 [英] mySQL query for selecting children

问题描述

我不知道这是否可能在mySQL。以下是我的表格： -

类别表格：

• id


• 姓名


• parent_id（指向Categories.id）

我使用上表来映射所有类别和子类别。

产品表：

• id



$ b

Products表中的category_id指向其所属的子类别ID。





例如如果我有玩具>教育> ABC，其中ABC是产品，玩具是类别和教育是子类别，那么ABC将category_id作为2.





现在的问题是，我想使用SQL查询来显示特定类别的所有产品（所有子类别及其子类别... n级别）。





例如：

  select * from categories，products where category.name ='Toys'and .... 
  

上述查询应显示教育产品以及所有其他子类别及其子类别。





这可能使用mySQL查询吗？如果没有什么选择，我有？我想避免PHP递归。





更新：基本上，我想显示前10个产品在主类别，我会做通过在产品表中添加hits列。

解决方案

我在之前的项目中做了什么，同样的事情，我添加了两个新列。






• i_depth：类别深度的整数值


• nvc_breadcrumb：类别的完整路径以面包屑类型的格式






然后我添加了一个触发器到包含类别信息的表中，更新是在同一触发器）...

   - 重置所有分支
 UPDATE t_org_branches 
 SET nvc_breadcrumb = NULL，
 i_depth = NULL 
 
  - 首先更新根分支
 UPDATE t_org_branches 
 SET nvc_breadcrumb ='/'，
 i_depth = 0 
 WHERE guid_branch_parent_id IS NULL 
 
  - 更新循环上的子分支
 WHILE EXISTS（SELECT * FROM t_branches WHERE i_Depth IS NULL）
 UPDATE tobA 
 SET tobA.i_depth = tobB.i_depth + 1，
 tobA.nvc_breadcrumb = tobB.nvc_breadcrumb + Ltrim（tobA.guid_branch_parent_id）+'/'
 FROM t_org_branches AS tobA 
 INNER JOIN t_org_branches AS tobB ON（tobA.guid_branch_parent_id = tobB.guid_branch_id）
 WHERE tobB.i_depth> = 0 
 AND tobB.nvc_breadcrumb IS NOT NULL 
和tobA.i_depth IS NULL 
  

然后只需在类别ID上加入您的产品表格，并执行LIKE％/ [CATEGORYID] /％ '。请记住，这是在MS SQL中完成的，但应该很容易翻译成MySQL版本。





它可能只是足够兼容剪切和粘贴（表和列名称更改后）。





---





扩展说明...





t_categories（现在代表）...

  Cat Parent CategoryName 
 1 NULL MyStore 
 2 1电子产品
 3 1服装
 4 1图书
 5 2电视
 6 2立体声
 7 5等离子体
 8 5 LCD 
  

t_categories（修改后）...

  Cat父类别名称深度面包屑
 1 NULL MyStore NULL NULL 
 2 1电子设备NULL NULL 
 3 1服装NULL NULL 
 4 1 NULL 
 5 2电视NULL NULL 
 6 2立体声NULL NULL 
 7 5等离子体NULL NULL 
 8 5 LCD NULL NULL 
  

t_categories（使用我给出的脚本后）

  Cat Parent CategoryName Depth Breadcrumb 
 1 NULL MyStore 0 / 
 2 1电子产品1/1 / 
 3 1服装1/1 / 
 4 1图书1/1 / 
 5 2电视2/1/2 / 
 6 2立体声2/1/2 / 
 7 5 LCD 3/1/2/5 / 
 8 7 Samsung 4/1/2 / 5/7 / 
  

t_products（就像你现在没有修改） p>



  ID猫名称
 1 8三星LNT5271F 
 2 7液晶电视安装座，高达36
 3 7液晶电视安装，最大52
 4 5 HDMI电缆，6ft 
  

加入类别和产品（类别为C，产品为P）

  C.Cat父类别名称深度面包屑ID p.Cat名称
 1 NULL MyStore 0 / NULL NULL NULL 
 2 1电子设备1/1 / NULL NULL NULL 
 3 1服装1/1 / NULL NULL NULL 
 4 1图书1/1 / NULL NULL NULL 
 5 2电视2/1/2/4 5 HDMI电缆，6ft 
 6 2立体声2/1/2 / NULL NULL NULL 
 7 5 LCD 3/1 / 2/5/2 7液晶电视安装，最高36
 7 5 LCD 3/1/2/5/3 7液晶电视安装，最高52
 8 7 Samsung 4/1 / 2/5/7/1 8三星LNT5271F 
  

现在假设产品表格更完整在每个类别中有东西，没有NULL，你可以做一个Breadcrumb LIKE'％/ 5 /％'，得到我提供的最后一个表的最后三个项目。请注意，它包括直接项目和类别的孩子（如三星电视）。如果你只想要特定的类别项目，只要做一个c.cat = 5。




I am not sure if this is possible in mySQL. Here are my tables:-

Categories table:

• id
• name
• parent_id (which points to Categories.id)

I use the above table to map all the categories and sub-categories.

Products table:

• id
• name
• category_id

The category_id in the Products table points to the sub-category id in which it belongs.

e.g. If I have Toys > Educational > ABC where ABC is product, Toys is Category and Educational is sub Category, then ABC will have category_id as 2.

Now the problem is that I want to use a SQL query to display all the products (in all the sub-categories and their sub-categories.. n level) for a particular category.

e.g.:

select * from categories,products where  category.name = 'Toys' and ....

The above query should display the products from Educational also and all other sub categories and their subcategories.

Is this possible using a mySQL query? If not what options do I have? I would like to avoid PHP recursion.

Update: Basically I want to display the top 10 products in the main category which I will be doing by adding a hits column to products table.

解决方案

What I've done in previous projects where I've needed to do the same thing, I added two new columns.

• i_depth: int value of how deep the category is
• nvc_breadcrumb: complete path of the category in a breadcrumb type of format

And then I added a trigger to the table that houses the category information to do the following (all three updates are in the same trigger)...

-- Reset all branches
UPDATE t_org_branches
    SET nvc_breadcrumb = NULL,
    i_depth = NULL

-- Update the root branches first
UPDATE t_org_branches 
    SET nvc_breadcrumb = '/', 
        i_depth = 0 
    WHERE guid_branch_parent_id IS NULL

-- Update the child branches on a loop
WHILE EXISTS (SELECT * FROM t_branches WHERE i_depth IS NULL) 
    UPDATE tobA 
        SET tobA.i_depth = tobB.i_depth + 1, 
            tobA.nvc_breadcrumb = tobB.nvc_breadcrumb + Ltrim(tobA.guid_branch_parent_id) + '/' 
        FROM t_org_branches AS tobA
            INNER JOIN t_org_branches AS tobB ON (tobA.guid_branch_parent_id = tobB.guid_branch_id) 
        WHERE tobB.i_depth >= 0 
            AND tobB.nvc_breadcrumb IS NOT NULL 
            AND tobA.i_depth IS NULL

And then just do a join with your products table on the category ID and do a "LIKE '%/[CATEGORYID]/%' ". Keep in mind that this was done in MS SQL, but it should be easy enough to translate into a MySQL version.

It might just be compatible enough for a cut and paste (after table and column name change).

---

Expansion of explanation...

t_categories (as it stands now)...

Cat Parent  CategoryName
1   NULL    MyStore
2   1       Electronics
3   1       Clothing
4   1       Books
5   2       Televisions
6   2       Stereos
7   5       Plasma
8   5       LCD

t_categories (after modification)...

Cat  Parent  CategoryName   Depth   Breadcrumb
1   NULL    MyStore         NULL    NULL    
2   1       Electronics     NULL    NULL
3   1       Clothing        NULL    NULL
4   1       Books           NULL    NULL
5   2       Televisions     NULL    NULL
6   2       Stereos         NULL    NULL
7   5       Plasma          NULL    NULL
8   5       LCD             NULL    NULL

t_categories (after use of the script I gave)

Cat  Parent  CategoryName   Depth   Breadcrumb
1   NULL    MyStore         0       /   
2   1       Electronics     1       /1/
3   1       Clothing        1       /1/
4   1       Books           1       /1/
5   2       Televisions     2       /1/2/
6   2       Stereos         2       /1/2/
7   5       LCD             3       /1/2/5/
8   7       Samsung         4       /1/2/5/7/

t_products (as you have it now, no modifications)...

ID   Cat Name
1   8   Samsung LNT5271F
2   7   LCD TV mount, up to 36"
3   7   LCD TV mount, up to 52"
4   5   HDMI Cable, 6ft

Join categories and products (where categories is C, products is P)

C.Cat Parent CategoryName   Depth   Breadcrumb  ID   p.Cat  Name
1    NULL   MyStore         0       /           NULL NULL   NULL
2    1      Electronics     1       /1/         NULL NULL   NULL
3    1      Clothing        1       /1/         NULL NULL   NULL
4    1      Books           1       /1/         NULL NULL   NULL
5    2      Televisions     2       /1/2/       4    5      HDMI Cable, 6ft
6    2      Stereos         2       /1/2/       NULL NULL   NULL
7    5      LCD             3       /1/2/5/     2    7      LCD TV mount, up to 36"
7    5      LCD             3       /1/2/5/     3    7      LCD TV mount, up to 52"
8    7      Samsung         4       /1/2/5/7/   1    8      Samsung LNT5271F

Now assuming that the products table was more complete so that there is stuff in each category and no NULLs, you could do a "Breadcrumb LIKE '%/5/%'" to get the last three items of the last table I provided. Notice that it includes the direct items and children of the category (like the Samsung tv). If you want ONLY the specific category items, just do a "c.cat = 5".

这篇关于mySQL查询选择子项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！