两个红框输出的不可能是多条记录，只能是像标量子查询输出一条。这个写得相当绕，既然最终只输出一行，那简化成：select agentid,agentname from pmagtinf where parentid='AGE...001'; 即可。 

刘老师，根据传入的每一个不同agentid值, 逐级找到它的所有parentid，这个是不是在 in后的括号里实现的？select agentid from 表 start witg agebtud=:a connect by prior agentid=parentid, 这里prior agentid=parentid， 翻译出来是当前记录的agentid等于后面一条记录的parentid, 换句话说，是从祖先（比如祖先agentid是AGE0000..01）开始，找出它的子子孙孙吧？ 

create table tb_zxp (agentid varchar2(5) primary key,agentname varchar2(20),parentid varchar2(5));
insert into tb_zxp values ('008','AABBCCDD','007');
insert into tb_zxp values ('007','AABBCC','006');
insert into tb_zxp values ('006','AABB','005');
insert into tb_zxp values ('005','AA',null);
commit;

为了方便，输出里加了agentid一列，发现两个子查询不会输出多条记录，确属标量子查询
从agentid='005'开始往下找，看输出结果：
select
(select agentid from tb_zxp p where p.parentid='005' start with p.agentid=pa.agentid connect by prior p.parentid=p.agentid) low_agentid,
(select agentname from tb_zxp p where p.parentid='005' start with p.agentid=pa.agentid connect by prior p.parentid=p.agentid) low_agentname，
agentid
from tb_zxp pa where parentid in
(select agentid from tb_zxp start with agentid='005' connect by prior agentid=parentid) ;
LOW_AGENTID LOW_AGENTNAME AGENTID
--------------- ------------------------------------------------------------ ---------------
006                  AABB                       008
006                 AABB                        007
006                AABB                        006

解释：
A. (select agentid from tb_zxp start with agentid='005' connect by prior agentid=parentid) 查出从祖先开始一级级往下的agentid
B. from tb_zxp pa where parentid in (...) 从A的结果里，剔除祖先这行，输出其余子孙行的记录，每行的agentid被后续两个子查询引用

现在从agentid='006'开始往下找，看输出结果：
select
(select agentid from tb_zxp p where p.parentid='006' start with p.agentid=pa.agentid connect by prior p.parentid=p.agentid) low_agentid,
(select agentname from tb_zxp p where p.parentid='006' start with p.agentid=pa.agentid connect by prior p.parentid=p.agentid) low_agentname，
agentid
from tb_zxp pa where parentid in
(select agentid from tb_zxp start with agentid='006' connect by prior agentid=parentid) ;
LOW_AGENTID LOW_AGENTNAME AGENTID
--------------- ------------------------------------------------------------ ---------------
007 AABBCC 008
007 AABBCC 007

暂且不分析子查询里的逻辑，看输出结果，反映了两类信息：
儿子辈的agentid和agentname
究竟有几代人（从输出的行数可知）

由此，把SQL改写如下：
with
t1 as
(select level from tb_zxp start with agentid='006' connect by prior agentid=parentid),
t2 as
(select agentid,agentname from tb_zxp where parentid='005')
select agentid,agentname from t1,t2;

AGENTID AGENTNAME
--------------- ------------------------------------------------------------
006 AABB
006 AABB
006 AABB

纠正下，最后的SQL应该如下：

with
t1 as
(select lv from (select level as lv,connect_by_isleaf as isleaf from tb_zxp start with agentid='005' connect by prior agentid=parentid) where isleaf=0),
t2 as
(select agentid,agentname from tb_zxp where parentid='005')
6 select agentid,agentname from t1,t2;

AGENTID AGENTNAME
--------------- ------------------------------------------------------------
006         AABB
006         AABB
006         AABB