MySQL：在一个Statement上连接许多表

Question

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

TABLE product_type ：

id,name, ....

TABLE product ：

id,parent_id, name, ...

parent_id ：是product_type ID

TABLE treeNode ：

id, parent_id, name, type

它是一个树层次结构（根有n个子节点），层次结构的数量 未知 。

coltype的值为 “ CATEGORY” 或 “ GROUP” ，这意味着我有2棵树：

Categories：

   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
                  ....

Groups：

   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
                  ....

TABLE linked_treeNode：

product_id, treeNode_id

现在说，用户选择：

1：a product type（PARAM： $selected_type）

2：a category（PARAM： $selected_cat）

3：a group（PARAM： $selected_group）

现在，我想展示符合这些选择的 all Products ：

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 ..etc尝试了许多其他Statements（更改）。但没有成功。

非常感谢

编辑：我上面使用的语句是错误的，所以不要使用它！

Answer 1

对于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

亲爱的，不是吗？

第二选择：等级编号

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

级别编号比应用程序中的 级别代码 容易维护。

使用级别号来获取所有后代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的那两个子查询。

笔记：

除非按级别编号或级别代码订购，否则它们将无法工作。您可以测试在这个最后的查询[SqlFiddle](http://sqlfiddle.com/#!2/8150e/12)
，通过改变order by level来order by id看到的差异。

另一个选择：嵌套集模型

请参考此博客，我尚未测试。但是我认为这类似于最后两个选择。

它需要在treenode表中添加一个左数字和一个右数字，以将它们之间的所有后代ID括起来。