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 '--'||b.bname title,a.aid,2 no from a,b where a.aid=b.aid
union all
select aname,aid,1 from a)
order by aid,no,title
(select a.name name, 'a'||a.id id, null pid from t_a a
union
select b.name, b.id||'', 'a'||b.aid pid from t_b b) start with pid is null connect by prior id = pid;
scott@ORCL10G> INSERT INTO A(aid, aname) VALUES(1,'1号楼');已创建 1 行。scott@ORCL10G> INSERT INTO A(aid, aname) VALUES(2,'2号楼');已创建 1 行。scott@ORCL10G>
scott@ORCL10G> INSERT INTO B(bid, bname, aid) VALUES(1,'1层',1);已创建 1 行。scott@ORCL10G> INSERT INTO B(bid, bname, aid) VALUES(2,'2层',1);已创建 1 行。scott@ORCL10G> INSERT INTO B(bid, bname, aid) VALUES(3,'1层',3);已创建 1 行。scott@ORCL10G> INSERT INTO B(bid, bname, aid) VALUES(4,'2层',2);已创建 1 行。scott@ORCL10G> INSERT INTO B(bid, bname, aid) VALUES(5,'3层',2);已创建 1 行。scott@ORCL10G>
scott@ORCL10G> SELECT LPAD(t.name,5,'-') AS name FROM (
2 SELECT aname AS name, aid AS "LEVEL" FROM a
3 UNION ALL
4 SELECT bname AS name, aid AS "LEVEL" FROM b ) t
5 ORDER BY t."LEVEL" ASC;NAME
----------
1号楼
--2层
--1层
--3层
--2层
2号楼
--1层已选择7行。scott@ORCL10G>
scott@ORCL10G> INSERT INTO A(aid, aname) VALUES(1,'1号楼');已创建 1 行。scott@ORCL10G> INSERT INTO A(aid, aname) VALUES(2,'2号楼');已创建 1 行。scott@ORCL10G>
scott@ORCL10G> INSERT INTO B(bid, bname, aid) VALUES(1,'1层',1);已创建 1 行。scott@ORCL10G> INSERT INTO B(bid, bname, aid) VALUES(2,'2层',1);已创建 1 行。scott@ORCL10G> INSERT INTO B(bid, bname, aid) VALUES(3,'1层',2);已创建 1 行。scott@ORCL10G> INSERT INTO B(bid, bname, aid) VALUES(4,'2层',2);已创建 1 行。scott@ORCL10G> INSERT INTO B(bid, bname, aid) VALUES(5,'3层',2);已创建 1 行。scott@ORCL10G>
scott@ORCL10G> SELECT LPAD(t.name,5,'-') AS name FROM (
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>
scott@ORCL10G> DROP TABLE A;表已删除。scott@ORCL10G> DROP TABLE B;表已删除。scott@ORCL10G>
SELECT ANAME FROM
(SELECT A.*,0 RN FROM A
UNION ALL
SELECT B.AID,'--'||B.BNAME,1 RN FROM B) ORDER BY AID,RN,ANAME