mysql查询帮助显示层次结构 [英] mysql query help for an hierarchy display

问题描述

我有一张桌子:

id  type_name               type_reportToId 
1   Administrator               0   
2   Guest   GUEST               1   
3   Landlord                    1   
4   Property Manager            3   
13  Manager Manager             3   
14  Asst. Property Manager     13   

我需要ID为14的向层次结构报告"的结果，例如:ID 14必须报告13,13必须报告3,3必须报告1 ...就像明智的做法.我需要在mysql中查询.有谁能够帮我 ?

I need a result of "report to hierarchy" for id No 14. eg: id 14 has to report 13,13 has to report 3,3 has to report 1 ...like wise. I need a query for that in mysql. Can anybody help me ?

推荐答案

您最好在应用程序逻辑中而不是在查询中这样做.

You preferably do that in your application logic rather than in your query.

伪代码

query=SELECT id, type_name, type_reportToId FROM table ORDER BY id ASC, report_toId ASC

// loop through rows
currentParent = 0
processed = 0
hierarachyLevel = 0
while ( processed < foundrows ) 
{
     while ( row = fetchrow( dbResult ) )
    {
         if ( row[type_reportToId] == currentParent )
         {
             resultHierarchy[hierarchyLevel] = row
             currentParent = row[id]
             processed++
             hierarchyLevel++
             dataseek( dbResult, 0 )
             break
         }
   }

}

这篇关于mysql查询帮助显示层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！