Question

我的感召性名单结构类似

“entergraph

页: 1

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name =  ELECTRONICS ;

取得这样的结果

“entergraph

但是,如果想取得这样的结果,就没有任何选择。

“entergraph

预先感谢

Answer 1

不幸的是,很难把目前设置的一类子类数量算出。页: 1 阁下补充说,也不可能说明哪些类别与某一类别有直接联系,然后是更深入的一类。

解决这一问题的一个极好的解决办法是使用nested set structure for their menu, 。 http://www.fliquidstudios.com/2008/12/23/nested-set-in-mysql/“rel=“nofollow”>here 。

Answer 2

你需要一个联合国构想,我建议删除各自层次的条目重复。此外,如果你打算像在网络上那样向用户提供,就不需要列入“联合国扫盲十年”的价值。

select L1.Category_ID,
       L1.name, 
       "1" as HierarchyLevel, 
       count( L2.Category_ID ) as NextLevelCount
   from Category L1
        LEFT JOIN Category L2
           on L1.Category_ID = L2.Parent
   where L1.name = "ELECTRONICS"
   group by L1.Category_ID
UNION
select L2.Category_ID,
       L2.name, 
       "2" as HierarchyLevel,
       count( L3.Category_ID ) as NextLevelCount
   from Category L1
        JOIN Category L2
           on L1.Category_ID = L2.Parent
           LEFT JOIN Category L3
              on L2.Category_ID = L3.Parent
   where L1.name = "ELECTRONICS"
   group by L2.Category_ID
UNION
select L3.Category_ID,
       L3.name, 
       "3" as HierarchyLevel,
       count( L4.Category_ID ) as NextLevelCount
   from Category L1
        JOIN Category L2
           on L1.Category_ID = L2.Parent
           JOIN Category L3
              on L2.Category_ID = L3.Parent
              LEFT JOIN Category L4
                 on L3.Category_ID = L4.Parent
   where L1.name = "ELECTRONICS"
   group by L3.Category_ID
UNION
select L4.Category_ID,
       L4.name, 
       "4" as HierarchyLevel,
       1 as NextLevelCount
   from Category L1
        JOIN Category L2
           on L1.Category_ID = L2.Parent
           JOIN Category L3
              on L2.Category_ID = L3.Parent
              JOIN Category L4
                 on L3.Category_ID = L4.Parent
   where L1.name = "ELECTRONICS"

这显然是固定到4级的,但会将它们合并为单一名单,但不会重复你提出的内容。我也只是为了参考目的而打上等级。为了达到每个级别,你必须做一个LEFT JOIN到下一级别,否则,你就会在你试图检索的级别上错过可能的项目,但其他情况下,SHOULD仍然是INNER JOIN。

如果你处理产品,我想,如果在四级之后,他们就会发现一些东西,就会有一个更大的问题:

Answer 3

作为一个关注点,因为它可能工作太多,而且与你必须做的“回答”不同,我建议你研究“经过改良的柏威”的概念。 http://www.sitepoint.com/hierarchical-data-base-2/“rel=“nofollow”>explanation by Gijs Van Tulder 。

这需要在桌上增加储存等级数据的领域,还需要额外的代码来保持这一结构,但能够使树木结构更容易地在连接数据库引擎中处理。

Basically, as well as the usual parent_id that we store with trees, we also store a left and a right value. When correctly populated, it s then easy to get "all children of node" by simply selecting all nodes where node.left > parent.left and node.right < parent.right, regardless of the depth of the nodes.

Answer 4

你们可以这样做:

SELECT name FROM category WHERE name =  ELECTRONICS 

UNION

SELECT t2.name FROM t2 INNER JOIN category as t1 ON t2.parent = t1.category_id WHERE t1.name =  ELECTRONICS 

UNION

SELECT t3.name FROM t3 INNER JOIN category as t1 ON t3.parent = t1.category_id WHERE t1.name =  ELECTRONICS 

UNION

SELECT t4.name FROM t4 INNER JOIN category as t1 ON t4.parent = t1.category_id WHERE t1.name =  ELECTRONICS

友情链接