-- 哪位利用level connect by 帮我写个例子我学习下。数据和结果如下:--测试数据
create TABLE tb(ID char(3),PID char(3),Name varchar(10))
INSERT into tb SELECT '001',NULL ,'山东省' from dual
UNION ALL SELECT '002','001','烟台市' from dual
UNION ALL SELECT '004','002','招远市' from dual
UNION ALL SELECT '003','001','青岛市' from dual
UNION ALL SELECT '005',NULL ,'四会市' from dual
UNION ALL SELECT '006','005','清远市' from dual
UNION ALL SELECT '007','006','小分市' from dual
/*--结果
ID PID Name
------ --------- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 NULL 四会市
006 005 清远市
007 006 小分市
--*/
create TABLE tb(ID char(3),PID char(3),Name varchar(10))
INSERT into tb SELECT '001',NULL ,'山东省' from dual
UNION ALL SELECT '002','001','烟台市' from dual
UNION ALL SELECT '004','002','招远市' from dual
UNION ALL SELECT '003','001','青岛市' from dual
UNION ALL SELECT '005',NULL ,'四会市' from dual
UNION ALL SELECT '006','005','清远市' from dual
UNION ALL SELECT '007','006','小分市' from dual
/*--结果
ID PID Name
------ --------- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 NULL 四会市
006 005 清远市
007 006 小分市
--*/
with tb as (SELECT '001' id,NULL pid,'山东省' name from dual
UNION ALL SELECT '002','001','烟台市' from dual
UNION ALL SELECT '004','002','招远市' from dual
UNION ALL SELECT '003','001','青岛市' from dual
UNION ALL SELECT '005',NULL ,'四会市' from dual
UNION ALL SELECT '006','005','清远市' from dual
UNION ALL SELECT '007','006','小分市' from dual)
select * from tb start with id in ('001', '005') connect by prior id = pid
SQL> with tb as (SELECT '001' id,NULL pid,'山东省' name from dual
2 UNION ALL SELECT '002','001','烟台市' from dual
3 UNION ALL SELECT '004','002','招远市' from dual
4 UNION ALL SELECT '003','001','青岛市' from dual
5 UNION ALL SELECT '005',NULL ,'四会市' from dual
6 UNION ALL SELECT '006','005','清远市' from dual
7 UNION ALL SELECT '007','006','小分市' from dual)
8 select * from tb start with id in ('001', '005') connect by prior id = pid
9 ;ID PID NAME
--- --- ---------
001 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 四会市
006 005 清远市
007 006 小分市7 rows selectedSQL>
FROM tb t
START WITH t.pid IS NULL
CONNECT BY PRIOR t.id = t.pid;
by prior id = pid;ID PID
--- ---
NEWNAME
--------------------------------------------------------------------------------
001
山东省002 001
烟台市004 002
招远市
ID PID
--- ---
NEWNAME
--------------------------------------------------------------------------------
003 001
青岛市005
四会市006 005
清远市
ID PID
--- ---
NEWNAME
--------------------------------------------------------------------------------
007 006
小分市
7 rows selected.
SQL> with tb as (SELECT '001' id,NULL pid,'山东省' name from dual
2 UNION ALL SELECT '002','001','烟台市' from dual
3 UNION ALL SELECT '004','002','招远市' from dual
4 UNION ALL SELECT '003','001','青岛市' from dual
5 UNION ALL SELECT '005',NULL ,'四会市' from dual
6 UNION ALL SELECT '006','005','清远市' from dual
7 UNION ALL SELECT '007','006','小分市' from dual)
8 select t.*,level from tb t start with id in ('001', '005') connect by prior id = pid
9 ;ID PID NAME LEVEL
--- --- --------- ----------
001 山东省 1
002 001 烟台市 2
004 002 招远市 3
003 001 青岛市 2
005 四会市 1
006 005 清远市 2
007 006 小分市 37 rows selectedSQL> prior 比如过level=2 如果使用 prior id = pid将去找level=1的数据
比如过level=3 如果使用 prior id = pid将去找level=2的数据