经典SQL语句_树的优化

oracle 语法

上次发的返回生成树的：
select ob_parentcode_0047,ob_sortcode_0047,ob_sortname_0047 from ctb_text_0047 where ob_isvalid_0047 = '1'
connect by prior ob_sortcode_0047=ob_parentcode_0047 start with ob_parentcode_0047='01' ORDER SIBLINGS BY ob_sortcode_0047

只能在9i中用，在8i中只能用，IOS用的是8i的数据库：

select ob_parentcode_0047,ob_sortcode_0047,ob_sortname_0047 from ctb_text_0047 where ob_isvalid_0047 = '1'
connect by prior ob_sortcode_0047=ob_parentcode_0047 start with ob_parentcode_0047='01' ORDER BY ob_sortcode_0047

将 ORDER SIBLINGS BY  改为 ORDER  BY

返回生成好的树

 

BCB中显示树的代码：

///定义一个结构保存返回的记录集。
                         /*struct {
          char psortcode[21];  //父类编码
          char sortcode[21];   //类别编码
          char sortname[61];   //类别名称
       }sort;
       */
                          TreeView1->Items->Clear();
                          TTreeNode *Tnode1;
                          TTreeNode *Tnode2;
                          String s_pcode="";  //临时保存父类编码
                          String s_code="";   //临时保存类别编码
                          for(int i=0; i< arr_sort.size(); i++)
                          {
                              if(s_pcode=="")
                              {s_pcode=String(arr_sort[i].psortcode);}
                              if(AnsiString(arr_sort[i].psortcode)=="01")  //顶级节点父类编码=01)
                              {
                                  Tnode1=TreeView1->Items->AddChildObject(NULL,arr_sort[i].sortname,arr_sort[i].sortcode);
                              }
                              else if(s_pcode==String(arr_sort[i].psortcode))  //与上一条记录是同级（父类代码相同）
                              {
                                  Tnode2=TreeView1->Items->AddObject(Tnode1,arr_sort[i].sortname,arr_sort[i].sortcode);
                                  Tnode1=Tnode2;
                              }
                              else if(s_code == String(arr_sort[i].psortcode))  //是上一条记录的下级（上一条的类别代码=本条的父类代码）
                              {
                                  Tnode2=TreeView1->Items->AddChildObject(Tnode1,arr_sort[i].sortname,arr_sort[i].sortcode);
                                  Tnode1=Tnode2;
                              }
                              else  //不是上一条记录是同级/下级
                              {  
                                    do
                                    {
                                       Tnode1=Tnode1->Parent;
                                       if(Tnode1 == NULL)
                                       break;
                                    }while (strcmp(arr_sort[i].psortcode,(char*)(Tnode1->Data))); //找到本条的父类代码=类目代码的那一条记录，作为那一条的子节点
                                   
                                   Tnode2=TreeView1->Items->AddChildObject(Tnode1,arr_sort[i].sortname,arr_sort[i].sortcode);
                                   Tnode1=Tnode2;
                              }
                              s_pcode=String(arr_sort[i].psortcode);
                              s_code=String(arr_sort[i].sortcode);
                           }