嵌套查询

Question

建立了一张问题类型表，PROBLEMSTYPE
(
  PROBLEMTYPEID   类型ID,
  PROBLEMTYPENAME 类型名称,
  PROBLEMLEVEL    问题类型所在层次,
  PARENTTYPEID    父类型ID,
  TYPEPATH        路径,
  DESCRIPTION     描述
)问题类型是一层次结构,一个问题类型可以通过其PARENTTYPEID找到其上级问题类型，PROBLEMLEVEL记录该问题类型所在的层次，如何写查询语句，可以得到每个问题类型的根ID（即它的最高上级）

Answer 1

select CONNECT_BY_ROOT from PROBLEMSTYPE connect by prior PROBLEMTYPEID=PARENTTYPEID start with PROBLEMTYPEID='这里写你要查询的节点';

Answer 2

上面那个connect by 可能写反了
select CONNECT_BY_ROOT from PROBLEMSTYPE connect by PROBLEMTYPEID=prior PARENTTYPEID start with PROBLEMTYPEID='这里写你要查询的节点';

Answer 3

select CONNECT_BY_ROOT(PROBLEMTYPEID) 
from PROBLEMSTYPE 
connect by prior PROBLEMTYPEID=PARENTTYPEID 
start with PROBLEMTYPEID='这里写你要查询的节点';

Answer 4

晕，好像又写反了
select CONNECT_BY_ROOT(PROBLEMTYPEID) 
from PROBLEMSTYPE 
connect by PROBLEMTYPEID=prior PARENTTYPEID 
start with PROBLEMTYPEID='这里写你要查询的节点';

Answer 5

drop table PROBLEMSTYPE;
create table PROBLEMSTYPE(  
  PROBLEMTYPEID varchar2(20), 
  PROBLEMTYPENAME varchar2(20), 
  PROBLEMLEVEL varchar2(20), 
  PARENTTYPEID varchar2(20), 
  TYPEPATH  varchar2(200), 
  DESCRIPTION varchar2(200) 
);
insert into PROBLEMSTYPE select '0','类型名称0','问题类型所在层次0',null,'路径0','描述0' from dual;
insert into PROBLEMSTYPE select '1','类型名称1','问题类型所在层次1','0','路径1','描述1' from dual;
insert into PROBLEMSTYPE select '2','类型名称2','问题类型所在层次2','1','路径2','描述2' from dual;
insert into PROBLEMSTYPE select '3','类型名称3','问题类型所在层次3','1','路径3','描述3' from dual;
insert into PROBLEMSTYPE select '4','类型名称4','问题类型所在层次4','2','路径4','描述4' from dual;
insert into PROBLEMSTYPE select '5','类型名称5','问题类型所在层次5','3','路径5','描述5' from dual;
insert into PROBLEMSTYPE select '6','类型名称6','问题类型所在层次6','1','路径6','描述6' from dual;select CONNECT_BY_ROOT(PARENTTYPEID), PROBLEMSTYPE.*
from PROBLEMSTYPE 
start with PARENTTYPEID is null 
connect by prior PROBLEMTYPEID=PARENTTYPEID ;

Answer 6

drop table PROBLEMSTYPE;
create table PROBLEMSTYPE(  
  PROBLEMTYPEID varchar2(20), 
  PROBLEMTYPENAME varchar2(20), 
  PROBLEMLEVEL varchar2(20), 
  PARENTTYPEID varchar2(20), 
  TYPEPATH  varchar2(200), 
  DESCRIPTION varchar2(200) 
);
insert into PROBLEMSTYPE select '0','类型名称0','问题类型所在层次0',null,'路径0','描述0' from dual;
insert into PROBLEMSTYPE select '1','类型名称1','问题类型所在层次1','0','路径1','描述1' from dual;
insert into PROBLEMSTYPE select '2','类型名称2','问题类型所在层次2','1','路径2','描述2' from dual;
insert into PROBLEMSTYPE select '3','类型名称3','问题类型所在层次3','1','路径3','描述3' from dual;
insert into PROBLEMSTYPE select '4','类型名称4','问题类型所在层次4','2','路径4','描述4' from dual;
insert into PROBLEMSTYPE select '5','类型名称5','问题类型所在层次5','3','路径5','描述5' from dual;
insert into PROBLEMSTYPE select '6','类型名称6','问题类型所在层次6','1','路径6','描述6' from dual;select level as 层级,
       decode(level, 1,'*', 2,'**', 3,'***', 4,'****') 层次星星数,
        PROBLEMSTYPE.*
from PROBLEMSTYPE 
start with PARENTTYPEID is null 
connect by prior PROBLEMTYPEID=PARENTTYPEID ;--结果如下：
      层级 层次星星数 PROBLEMTYPEID        PROBLEMTYPENAME      PROBLEMLEVEL         PARENTTYPEID         TYPEPATH                                                                         DESCRIPTION
---------- ---------- -------------------- -------------------- -------------------- -------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 *          0                    类型名称0            问题类型所在层次0                         路径0                                                                            描述0
         2 **         1                    类型名称1            问题类型所在层次1    0                    路径1                                                                            描述1
         3 ***        2                    类型名称2            问题类型所在层次2    1                    路径2                                                                            描述2
         4 ****       4                    类型名称4            问题类型所在层次4    2                    路径4                                                                            描述4
         3 ***        3                    类型名称3            问题类型所在层次3    1                    路径3                                                                            描述3
         4 ****       5                    类型名称5            问题类型所在层次5    3                    路径5                                                                            描述5
         3 ***        6                    类型名称6            问题类型所在层次6    1                    路径6                                                                            描述67 rows selected

Answer 7

drop table PROBLEMSTYPE;
create table PROBLEMSTYPE(  
  PROBLEMTYPEID varchar2(20), 
  PROBLEMTYPENAME varchar2(20), 
  PROBLEMLEVEL varchar2(20), 
  PARENTTYPEID varchar2(20), 
  TYPEPATH  varchar2(200), 
  DESCRIPTION varchar2(200) 
);
insert into PROBLEMSTYPE select '0','类型名称0','问题类型所在层次0',null,'路径0','描述0' from dual;
insert into PROBLEMSTYPE select '1','类型名称1','问题类型所在层次1','0','路径1','描述1' from dual;
insert into PROBLEMSTYPE select '2','类型名称2','问题类型所在层次2','1','路径2','描述2' from dual;
insert into PROBLEMSTYPE select '3','类型名称3','问题类型所在层次3','1','路径3','描述3' from dual;
insert into PROBLEMSTYPE select '4','类型名称4','问题类型所在层次4','2','路径4','描述4' from dual;
insert into PROBLEMSTYPE select '5','类型名称5','问题类型所在层次5','3','路径5','描述5' from dual;
insert into PROBLEMSTYPE select '6','类型名称6','问题类型所在层次6','1','路径6','描述6' from dual;select level as 层级,
       decode(level, 1,'*', 2,'**', 3,'***', 4,'****') 层次星星数,
       CONNECT_BY_ROOT(PROBLEMTYPEID) 最顶层,
        PROBLEMSTYPE.*
from PROBLEMSTYPE 
start with PARENTTYPEID is null 
connect by prior PROBLEMTYPEID=PARENTTYPEID ;--结果如下：
      层级 层次星星数 最顶层               PROBLEMTYPEID        PROBLEMTYPENAME      PROBLEMLEVEL         PARENTTYPEID         TYPEPATH                                                                         DESCRIPTION
---------- ---------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 *          0                    0                    类型名称0            问题类型所在层次0                         路径0                                                                            描述0
         2 **         0                    1                    类型名称1            问题类型所在层次1    0                    路径1                                                                            描述1
         3 ***        0                    2                    类型名称2            问题类型所在层次2    1                    路径2                                                                            描述2
         4 ****       0                    4                    类型名称4            问题类型所在层次4    2                    路径4                                                                            描述4
         3 ***        0                    3                    类型名称3            问题类型所在层次3    1                    路径3                                                                            描述3
         4 ****       0                    5                    类型名称5            问题类型所在层次5    3                    路径5                                                                            描述5
         3 ***        0                    6                    类型名称6            问题类型所在层次6    1                    路径6                                                                            描述67 rows selected