oracle数据库中有两表 A,B
A(aid,aname)
1 1号楼
2 2号楼 B(bid,bname,aid)
1 1层 1
2 2层 1
3 1层 2
4 2层 2
5 3层 2
能否用一条迭代语句写出如下树形结构 1号楼
--1层
--2层
2号楼
--1层
--2层
--3层
A(aid,aname)
1 1号楼
2 2号楼 B(bid,bname,aid)
1 1层 1
2 2层 1
3 1层 2
4 2层 2
5 3层 2
能否用一条迭代语句写出如下树形结构 1号楼
--1层
--2层
2号楼
--1层
--2层
--3层
SELECT aname AS name, aid AS "LEVEL" FROM a
UNION ALL
SELECT bname AS name, aid+0.1 AS "LEVEL" FROM b
) t
ORDER BY t."LEVEL" ASC, t.name;
BID BNAME ANAME
--- ----- -----
1 1层 1
2 2层 1
3 1层 2
4 2层 2
5 3层 2
SQL> SELECT * FROM A1;
AID ANAME
--- -----
1 1号
2 2号
SQL>
SQL> SELECT LEVEL,RPAD(' ',lEVEL) ||BNAME,aNAME
2 FROM (SELECT *
3 FROM TEST
4 UNION ALL
5 SELECT NULL, AID, NULL FROM A1)
6 CONNECT BY PRIOR bNAME = aNAME
7 START WITH aNAME IS NULL
8 ;
LEVEL RPAD('',LEVEL)||BNAME ANAME
---------- -------------------------------------------------------------------------------- -----
1 1
2 1层 1
2 2层 1
1 2
2 1层 2
2 2层 2
6 rows selected
SQL>
select a,RPAD(' ',lEVEL) ||b from (select floorid a,floorno b,buildid p from base_floor union all
select buildid a,buildno b,null p from base_build) connect by prior a=p start with p is null 为什么报ORA-01436: 用户数据中的 CONNECT BY 循环 其中括号中试图查出的结果是a b p
1 1层 1
2 2层 1
3 1层 2
4 2层 2
5 3层 2
1 1号
2 2号
2 SELECT aname AS name, aid AS "LEVEL" FROM a
3 UNION ALL
4 SELECT bname AS name, aid+0.1 AS "LEVEL" FROM b
5 ) t
6 ORDER BY t."LEVEL" ASC, t.name;NAME
----------
1号楼
--1层
--2层
2号楼
--1层
--2层
--3层已选择7行。scott@ORCL10G>