SELECT a.* FROM
(
SELECT t.prnt_id,t.assn_id,LEVEL FROM spmc_assn t
CONNECT BY PRIOR t.assn_id=t.prnt_id
START WITH t.prnt_id=-1
) a
WHERE a.level=2
(
SELECT t.prnt_id,t.assn_id,LEVEL FROM spmc_assn t
CONNECT BY PRIOR t.assn_id=t.prnt_id
START WITH t.prnt_id=-1
) a
WHERE a.level=2
SELECT a.* FROM
(
SELECT t.prnt_id,t.assn_id,LEVEL FROM spmc_assn t
START WITH t.prnt_id=-1
CONNECT BY PRIOR t.assn_id=t.prnt_id
) a
WHERE a.level=2
start with也如一楼所说的,放在connect by 前面世
select * from (select level from dual connect by level<5) a where a.level=1
ORA-01747: user.table.column, table.column 或列说明无效
SQL> select * from (select level lv from dual connect by level<5) a where a.lv=1;
LV
----------
1
将语句改成
SELECT a.* FROM
(
SELECT t.prnt_id,t.assn_id,LEVEL lv FROM spmc_assn t
CONNECT BY PRIOR t.assn_id=t.prnt_id
START WITH t.prnt_id=-1
) a
WHERE a.lv=2