一个sql请教
id name parentid
10011光仔热力公司10004
100013第三分公司10001
100012第二分公司10001
10001北京热力公司0
10002上海热力公司0
10004刘德华热力公司0
100011第一分公司10001
我想查询结果如下:
id name parentid name
10011光仔热力公司10004 刘德华热力公司
100013第三分公司10001 北京热力公司
100012第二分公司10001 北京热力公司
10001北京热力公司0
10002上海热力公司0
10004刘德华热力公司0
100011第一分公司10001 北京热力公司
如何实现?
[解决办法]
select t1.id,t1.name,t1.parentid,t2.name as parentname from test t1 left join test t2 on t1.parentid = t2.id
[解决办法]
with tab as
(select '10011' as ID, ' 光仔热力公司' as name, '10004' as parentid
from dual
union all
select '100013' as ID, '第三分公司' as name, '10001' as parentid
from dual
union all
select '100012' as ID, '第二分公司' as name, '10001' as parentid
from dual
union all
select '10001' as ID, '北京热力公司' as name, '0' as parentid
from dual
union all
select '10002' as ID, '上海热力公司' as name, '0' as parentid
from dual
union all
select '10004' as ID, '刘德华热力公司' as name, '0' as parentid
from dual
union all
select '100011' as ID, '第一分公司' as name, '10001' as parentid from dual)
select t1.*, t2.name as parent_name
from tab t1
left join tab t2
on t1.parentid = t2.id
