求教
表名-JOB
emplid reportsto position
甲 100 101
乙 101 102
丙 102 103 SELECT A.EMPLID ,A.REPORTS_TO,B.EMPLID
FROM PS_JOB A ,PS_JOB B
WHERE B.POSIION_NBR=A.REPORTS_TO实际结果
乙 101 甲
丙 102 乙
想要结果
甲 100
乙 101 甲
丙 102 乙
表名-JOB
emplid reportsto position
甲 100 101
乙 101 102
丙 102 103 SELECT A.EMPLID ,A.REPORTS_TO,B.EMPLID
FROM PS_JOB A ,PS_JOB B
WHERE B.POSIION_NBR=A.REPORTS_TO实际结果
乙 101 甲
丙 102 乙
想要结果
甲 100
乙 101 甲
丙 102 乙
select '甲' a, 100 b, 101 c from dual union all
select '乙' a, 101 b, 102 c from dual union all
select '丙' a, 102 b, 103 c from dual
)
select t1.a, t1.b, t2.a from tab1 t1, tab1 t2
where t1.b = t2.c(+)
order by t1.b
;with tab1 as (
select '甲' a, 100 b, 101 c from dual union all
select '乙' a, 101 b, 102 c from dual union all
select '丙' a, 102 b, 103 c from dual
)
select distinct t1.a, t1.b, first_value(prior t1.a) over(partition by t1.a order by level desc) cc
from tab1 t1
connect by prior t1.c = t1.b
and level <= 2
order by t1.b
;