mysql - 如何将 1 个表连接到产品和类别应用程序树中(2 个不同的表作为一个表)？

Question

我正在制作一棵产品和类别树。树接受任何类别中存在的产品，无论是父节点还是叶子。

这是我的 table

#category table
+------+-----------+-------------+
|  ID  |  cat_name | parent_cat  |
+------+-----------+-------------+
|   1  |     TV    |     NULL    |
+------+-----------+-------------+
|   2  |    LCD    |      1      |
+------+-----------+-------------+
|   3  |   PLASMA  |      1      |
+------+-----------+-------------+


#product table
+------+-----------+-----------+
|  ID  |  prd_name |  prd_cat  |
+------+-----------+-----------+
|   1  |     LG    |     1     |
+------+-----------+-----------+
|   2  |     HD    |     2     |
+------+-----------+-----------+
|   3  |    FHD    |     2     |
+------+-----------+-----------+

我想要的是进行选择查询来检索包含所有类别和产品的完整树。

我可以使用此查询检索类别

select cat1.cat_name lvl1, cat2.cat_name lvl2
from category cat1 left join category cat2 on cat1.ID = cat2.parent_cat
where cat1.parent_cat is null;

这给了我这样的东西

+------+------+
| lvl1 | lvl2 |
+------+------+
|  TV  |  LCD |
+------+------+
|  TV  |PLASMA|
+------+------+

当我尝试加入树中的产品时，我使用了此查询

select cat1.cat_name,cat2.cat_name,product.prd_name
from category cat1 left join category cat2 on cat1.ID = cat2.parent_cat
left join product on cat1.ID = product.prd_cat
where cat1.parent_cat is null;

但是那个sql给了我这个结果

+-----------+-----------+-----------+
|  cat_name |  cat_name |  prd_name |
+-----------+-----------+-----------+
|    TV     |    LCD    |     LG    |
+-----------+-----------+-----------+
|    TV     |   PLASMA  |     LG    |
+-----------+-----------+-----------+

我希望将产品表视为类别(2 级)表，因此如果电视等父节点有一些子类别和子产品，我会同时获取它们。 如何获得像这样的结果树:

+-------------+------------+------------+
| tree_lvl_1  | tree_lvl_2 | tree_lvl_3 |
+-------------+------------+------------+
|      TV     |     LG     |    NULL    |
+-------------+------------+------------+
|      TV     |     LCD    |     HD     |
+-------------+------------+------------+
|      TV     |     LCD    |     FHD    |
+-------------+------------+------------+
|      TV     |   PLASMA   |    NULL    |
+-------------+------------+------------+

电视(类别)> LG(产品)

TV(类别)> LCD(类别)> HD(产品)

TV(类别)> LCD(类别)> FHD(产品)

电视(类别)> 等 ionic (类别)

<小时/>编辑:这个 question您建议的是构建仅包含类别的树，我知道该怎么做。我的问题是我还有 "products" 表，它应该像类别一样作为节点加入树!我希望类别和产品表被视为一个表(节点)

Answer 1

您可以通过首先消除拥有两个表的复杂性来解决这个问题。创建一个将类别记录与产品记录组合在一起的 View ，如下所示:

create view combi as
  select 'P' as type, 
         id, 
         prd_name as name, 
         prd_cat as parent_id
  from   product
  union all
  select 'C' as type, 
         id,
         cat_name, 
         parent_cat
  from   category;

现在您可以基于此 View 进行查询，请确保在连接条件中添加类型要求:

select    lvl1.name as lvl1,
          lvl2.name as lvl2,
          lvl3.name as lvl3
from      combi as lvl1
left join combi as lvl2 on lvl1.id = lvl2.parent_id and lvl1.type = 'C'
left join combi as lvl3 on lvl2.id = lvl3.parent_id and lvl2.type = 'C'
where     lvl1.parent_id is null;

查看它在 SQLFiddle 上运行:

| lvl1 |   lvl2 |   lvl3 |
|------|--------|--------|
|   TV |    LCD |     HD |
|   TV |    LCD |    FHD |
|   TV |     LG | (null) |
|   TV | PLASMA | (null) |