SELECT ID,DECODE(ID, CODE, '', FIRST_VALUE(NAME) OVER (PARTITION BY CODE ORDER BY CODE DESC)) || NAME
FROM (
SELECT B.*, NVL(A.ID, B.ID) AS CODE
FROM B
LEFT JOIN A ON BB.ID = A.CODE
)
select
name
form
(select distinct a.code1 code,b.name name from a,b where a.code1=b.code) c
union
select
c.name || d.name
from
(select distinct a.code1 code,b.name name from a,b where a.code1=b.code) c,
(select distinct a.code2 code,b.name name from a,b where a.code2=b.code) d
where
a.code1=c.code
and a.code2=d.code
思路:分别找出部门号+部门名;科室号+科室名。组建两个子标,然后,分成只有[部门名],和[部门名科室名]两个子集。
select a.dept_id,c.deptname||a.deptname deptname from jxyangdept a,jxyanglink b,jxyangdept c
where a.dept_id=b.did(+) and b.id=c.dept_id(+)
order by deptname
SQL> select code, (select name from b where b.code=c.id) || (select name from b
where b.code=c.code) from
2 (select '' as id, code as code from b where instr(name, '部门') > 0
3 union all
4 select id, code from a
5 )c
6 connect by prior code = id start with id is null or id = ''
7 ;