表结构如下：
id name type
1 tom a
2 jack b
jobID personID jobtitle
1 1 deputy general manager
2 1 CEO
3 2 CIO
4 2 vice manager
要求结果如下：
name title
tom deputy general manager,CEO
jack vice manager,CIO
请大家指教，谢谢！
------解决方案--------------------
--测试数据
create table t1(id int, name varchar2(100), type varchar2(100));
insert into t1
select 1, 'tom ', 'a ' from dual union all
select 2, 'jack ', 'b ' from dual;
/
create table t2(jobID int, personID int, jobtitle varchar2(100));
insert into t2
select 1,1, 'deputy general manager ' from dual union all
select 2,1, 'CEO ' from dual union all
select 3,2, 'CIO ' from dual union all
select 4,2, 'vice manager ' from dual;
/
--执行查询
select name, substr(max(sys_connect_by_path(jobtitle, ', ')), 2) title
from (select jobtitle, name, row_number() over(partition by personid order by 1) rn
from (select name,personid,jobtitle from t1,t2 where t1.id=t2.personid))
start with rn = 1
connect by rn - 1 = prior rn and name = prior name
group by name;
/
--查询结果
name title
jack CIO,vice manager
tom deputy general manager,CEO
------解决方案--------------------
第二種方法就是寫個函數
假設 D表
id name type
1 tom a
2 jack b
E表
jID pID jobtitle
1 1 deputy general manager
2 1 CEO
3 2 CIO
4 2 vice manager
create or replace function fff(v_id number)
return varchar2 is
v_title varchar2(30);
begin
for c1 in (select title from e where pid=v_id ) loop
v_title:=v_title||' '||c1.title;
end loop;
return v_title;
end;
select distinct d.name, fff(d.id) from e,d where d.id=e.pid
------解决方案--------------------
select name,jobtitle from
(select name,personid,jobtitle from( select t1.name as name,p1.personid as personid,p1.jobtitle||','||p2.jobtitle as jobtitle,row_number()over(partition by p1.personid order by 1)as rw from t2 p1,t2 p2,t1
where p1.personid=p2.personid and p1.jobtitle<>p2.jobtitle and t1.id=p1.personid)
where rw=1)