表A
需要通过查询得到表B结果集
求SQL语句
------解决方案--------------------
with t as
(select 10000 role_id, null parent_role_id, '01' roleno
from dual
union all
select 100001 role_id, 10000 parent_role_id, '0101' roleno
from dual
union all
select 100002 role_id, 10000 parent_role_id, '0102' roleno
from dual
union all
select 100003 role_id, 100001 parent_role_id, '010101' roleno from dual)
select t2.*,
(select roleno from t t1 where t1.role_id = t2.parent_role_id) parent_roleno
from t t2
------解决方案--------------------
with temp as
(select role_id, parent_role_id,roleno from tablename)
select tp.role_id, tp.parent_role_id,tp.roleno,t.roleno as parent_roleno from temp tp left outer join tablename t on t.role_id = tp.parent_role_id
试试看，应该可以的