表结构如下:
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
请大家指教,谢谢!
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
v_title:=v_title ¦ ¦' ' ¦ ¦c1.title; 变成v_title:=v_title¦¦'' ¦¦c1.title;
(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)