☆★○●查询树型结构数据并与别的表关联?●○★☆很有代表性,建议管理员置顶!!!
应用树型结构数据来对计算机及其硬件进行管理,使用树型数据结构可以使数据量减少,而且便于维护与扩展。下面构成与组合只是试验数数据,在现实中不是那样子的,但用于例子我想够了。
表名: codename
描述:对计算机及硬件进行编码,这个表中有两类数据,一类时计算机名,另一类是硬件名,
建表SQL:
CREATE TABLE CODENAME
(
CODE VARCHAR2(3),
NAME VARCHAR2(14)
)
插入数据:
INSERT INTO CODENAME VALUES ('401','电脑401');
INSERT INTO CODENAME VALUES ('148','显示器');
INSERT INTO CODENAME VALUES ('149','鼠标');
INSERT INTO CODENAME VALUES ('341','键盘');
INSERT INTO CODENAME VALUES ('380','主机组合');
INSERT INTO CODENAME VALUES ('301','主板');
INSERT INTO CODENAME VALUES ('302','PIIIcpu');
表名: PStree
描述:对计算机编码及其组成硬件编码建立相关的父子关系
建表SQL:
create table PStree
(parentcode varchar2(3),
childcode varchar2(3),
begindate DATE,
enddate DATE
)
插入数据:
INSERT INTO PSTREE VALUES ('0','401',SYSDATE,TO_DATE('2099/01/01','YYYY/MM/DD'));
INSERT INTO PSTREE VALUES ('401','148',SYSDATE,TO_DATE('2099/01/01','YYYY/MM/DD'));
INSERT INTO PSTREE VALUES ('401','149',SYSDATE,TO_DATE('2099/01/01','YYYY/MM/DD'));
INSERT INTO PSTREE VALUES ('401','341',SYSDATE,TO_DATE('2099/01/01','YYYY/MM/DD'));
INSERT INTO PSTREE VALUES ('401','380',SYSDATE,TO_DATE('2099/01/01','YYYY/MM/DD'));
INSERT INTO PSTREE VALUES ('380','301',SYSDATE,TO_DATE('2099/01/01','YYYY/MM/DD'));
INSERT INTO PSTREE VALUES ('380','302',SYSDATE,TO_DATE('2099/01/01','YYYY/MM/DD'));
查询要求:显示"电脑401"的全部硬件组成编码码,并显示出其构成的层级结构,则执行如下语句
select lpad(' ',2*(level-1)) || childcode as childcode
from pstree
start with parentcode ='0' and childcode='401'
connect by prior childcode = parentcode ;
结果输出:
CHILDCODE
-----------------
401
148
149
341
380
301
302
显然这个结果通常不是我们所要求的,我们更希望得出这样的结果:
电脑401
显示器
鼠标
键盘
主机组合
主板
PIIIcpu
于是我用这样的SQL想得到结果,但是我失败了,
SQL> select lpad(' ',2*(level-1)) || name
2 from (select ps.parentcode as parentcode,ps.childcode as childcode,cn.name as name from pstree ps,codename cn where ps.childcode=cn.code)
3 start with parentcode ='0' and childcode='401'
4 connect by prior childcode = parentcode ;
SQL> select lpad(' ',2*(level-1)) || name
2 from (select ps.parentcode as parentcode,ps.childcode as childcode,cn.name as name from pstr
ee ps,codename cn where ps.childcode=cn.code)
3 start with parentcode ='0' and childcode='401'
4 connect by prior childcode = parentcode ;
错误提示:
from (select ps.parentcode as parentcode,ps.childcode as childcode,cn.name as name from pstree ps
*
ERROR 位于第 2 行:
ORA-01437: 无法连接 CONNECT BY
请大家帮我看看!即使您解决不了,这个问题也很有代表性。请大家UP,请大家关注,一起学习嘛!