我有以下数据库结构/层次结构:

表格product_type:

id,name, ....

表格产品:

id,parent_id, name, ...

parent_id : 是product_type id

表树节点:

id, parent_id, name, type

它是一个树状层次结构(根有 n 个子节点) 层次结构的级别数未知。

col type 的值为 "CATEGORY" 或 "GROUP"， 这意味着，我有 2 棵树:

类别:

   TreeNode 1
         sub 1.1
                sub.1.1.1
                sub.1.1.2
                  ....
         sub 1.2
                sub.1.2.1
                sub.1.2.2
                  ....
   TreeNode 2
         sub 2.1
                sub.2.1.1
                sub.2.1.2
                  ....
         sub 2.2
                sub.2.2.1
                sub.2.2.2
                  ....

群组:

   TreeNode 1
         sub 1.1
                sub.1.1.1
                sub.1.1.2
                  ....
         sub 1.2
                sub.1.2.1
                sub.1.2.2
                  ....
   TreeNode 2
         sub 2.1
                sub.2.1.1
                sub.2.1.2
                  ....
         sub 2.2
                sub.2.2.1
                sub.2.2.2
                  ....

表 linked_treeNode:

product_id, treeNode_id

现在假设，用户选择:

1:产品类型(参数:$selected_type)

2:类别(参数:$selected_cat)

3:一组(参数:$selected_group)

现在我想显示满足这些选择的所有产品:

1-) 链接到选定的类别或其子类别

和

2-) 链接到选定的组或其子组

和

3-) 链接到选定的产品类型

什么是 MySQL 语句( 1 语句)？

我试过这个:

SELECT P.* FROM 
product P, treeNode C, treeNode G, linked_TreeNode LC
WHERE 
p.parent_id='$selected_type' 
AND
( 
       C.type='CATEGORY' 
       AND 
       C.parent_id='$selected_cat' 
       AND 
       P.id=LC.product_id 
       AND 
       (LC.treeNode_id=C.id OR LC.treeNode_id='$selected_cat') 
)
AND
( 
       G.type='GROUP' 
       AND 
       G.parent_id='$selected_group' 
       AND 
       P.id=LC.product_id 
       AND 
       (LC.treeNode_id=G.id OR LC.treeNode_id='$selected_group') 
)
;

但我总是得到 0 个结果!

我尝试了许多其他语句(更改)，包括 JOINS 等。但没有成功。

非常感谢

编辑:我上面使用的声明是错误的，所以不要使用它!

最佳答案

如何在MySql中用递归查询获取树节点的所有后代？

确实是MySql的问题，也是这道题的重点，但是你还是有一些选择的。

假设您有这样的示例数据，虽然没有您的示例那么多，但足以证明:

create table treeNode(
id int, parent_id  int,  name varchar(10), type varchar(10),level int);
insert into treeNode 
(id, parent_id, name, type, level) values 
( 1,  0,  'C1    ', 'CATEGORY', 1),
( 2,  1,  'C1.1  ', 'CATEGORY', 2),
( 3,  2,  'C1.1.1', 'CATEGORY', 3),
( 4,  1,  'C1.2  ', 'CATEGORY', 2),
( 5,  4,  'C1.2.1', 'CATEGORY', 3),
( 3,  8,  'G1.1.1',    'GROUP', 3),
( 4,  9,  'G1.2  ',    'GROUP', 2),
( 5,  4,  'G1.2.1',    'GROUP', 3),
( 8,  9,  'G1.1  ',    'GROUP', 2),
( 9,  0,  'G1    ',    'GROUP', 1);

首选:关卡代码

如treeNode表中name列的示例数据。 (我不会用英文怎么说，评论我level code的正确表达。)

要获取 C1 或 G1 的所有后代，可以像这样简单:

select * from treeNode where type = 'CATEGORY' and name like 'C1%' ;
select * from treeNode where type = 'GROUP' and name like 'G1%' ;

我非常喜欢这种方式，甚至需要我们在应用程序保存treeNode之前生成这些代码。当我们有大量记录时，它会比递归查询或过程更有效。我认为这是一种很好的反规范化方法。

使用这种方法，您想要加入的语句可能是:

SELECT distinct p.* --if there is only one tree node for a product, distinct is not needed
FROM product p
JOIN product_type pt
     ON pt.id= p.parent_id -- to get product type of a product
JOIN linked_TreeNode LC
     ON LC.product_id= p.id -- to get tree_nodes related to a product
JOIN (select * from treeNode where type = 'CATEGORY' and name like 'C1%' ) C --may replace C1% to concat('$selected_cat_name','%')
     ON LC.treeNode_id = C.id
JOIN (select * from treeNode where type = 'GROUP' and name like 'G1%' ) G --may replace G1% to concat('$selected_group_name','%')
     ON LC.treeNode_id = G.id
WHERE pt.name = '$selected_type'  -- filter selected product type, assuming using product.name, if using product.parent_id, can save one join by pt like your original sql

很甜蜜，不是吗？

第二选择:级别数

将级别列附加到 treeNode 表，如 DDL 中所示。

在应用程序中，级别编号比级别代码更易于维护。

使用级别编号获取C1 或G1 的所有后代需要像这样的小技巧:

SELECT id, parent_id, name, type, @pv:=concat(@pv,',',id) as link_ids 
  FROM (select * from treeNode where type = 'CATEGORY' order by level) as t
  JOIN (select @pv:='1')tmp
 WHERE find_in_set(parent_id,@pv)
    OR find_in_set(id,@pv);
 -- get all descendants of `C1`

SELECT id, parent_id, name, type, @pv:=concat(@pv,',',id) as link_ids 
  FROM (select * from treeNode where type = 'GROUP' order by level) as t
  JOIN (select @pv:=',9,')tmp
 WHERE find_in_set(parent_id,@pv)
    OR find_in_set(id,@pv) ;

这种方法比第一种方法慢，但仍然比递归查询快。

省略了问题的完整 sql。只需要将C和G的那两个子查询替换成上面的两个查询就可以了。

注意:

类似的方法还有很多，比如here , here ，甚至 here .除非按级别编号或级别代码订购，否则它们将无法工作。您可以测试此 SqlFiddle 中的最后一个查询 将 order by level 更改为 order by id 以查看差异。

另一种选择:嵌套集模型

请引用这个blog ，我还没有测试。但我认为它类似于最后两个选择。

它需要你在树节点表中添加一个左数和一个右数，以将所有后代的id包含在它们之间。

关于MySQL:在一个语句中连接多个表，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/25644784/