一个职员有多个工号，职务时如何在一行显示，用一个sql实现

Question

我有
职员表：     employee(empid,name,age) 
             1，姓名1，25
             2，姓名2，22
工号对应表： empcode(empid,code)
                     1,1000
                     1,1001 
                     2,2000
职务对应表： emprole(empid,roleid)
                      1,10,
                      1,20
                      2,30
职务表：     role(roleid,rolename)
                    10, 班长
                    20，组长
                    30，组员
我想要实现这样的显示：
empid  name   age    code        rolename
  1    姓名1   25   1000，1001   班长，组长
  2    姓名2   22   2000         组员
或者一下面的方式显示：
  1    姓名1   25   1000        班长
                    1001        组长
  2    姓名2   22   2000        组员

Answer 1

写两个function，会更方便一些

Answer 2

---获得1000，1001...
CREATE OR REPLACE FUNCTION get_c2(empid NUMBER)  
RETURN VARCHAR2  
IS  
Col_c2 VARCHAR2(4000);  
BEGIN 
FOR cur IN (SELECT code FROM empcode WHERE id = empid) LOOP   
Col_c2 := Col_c2|| ',' || cur.code;  
END LOOP;  
Col_c2 := rtrim(Col_c2,1); 
RETURN Col_c2;  
END; 
/ --获得rolename
CREATE OR REPLACE FUNCTION get_c3(empid NUMBER)  
RETURN VARCHAR2  
IS  
Col_c2 VARCHAR2(4000);  
BEGIN 
FOR cur IN (SELECT r.rolename FROM emprole er, role r WHERE er.roleid = r.roleid(+) and er.id = empid) LOOP   
Col_c2 := Col_c2|| ',' || cur.code;  
END LOOP;  
Col_c2 := rtrim(Col_c2,1); 
RETURN Col_c2;  
END; 
/ 运行sql的到结果：
select e.empid, e.name, e.age, get_c2(code) code, get_c3(rolename) rolename
from employee e, empcode ec, emprole er, role r
where e.empid(+) = ec.empid and e.empid(+) = er.empid and er.roleid = r.roleid(+)

Answer 3

要是以工号和职务名称作为查询条件，那这个sql该怎么写啊？

Answer 4

这个sql的函数的参数应该是empid吧。如下这样。
select e.empid, e.name, e.age, get_c2(empid) code, get_c3(empid) rolename
from employee e, empcode ec, emprole er, role r
where e.empid(+) = ec.empid and e.empid(+) = er.empid and er.roleid = r.roleid(+)

Answer 5

select e.empid, e.name, e.age, get_c2(code) code, get_c3(rolename) rolename
from employee e, empcode ec, emprole er, role r
where e.empid(+) = ec.empid and e.empid(+) = er.empid and er.roleid = r.roleid(+)
group by e.empid忘了group by

Answer 6

问题解决了，用以下的方式。
CREATE OR REPLACE FUNCTION "get_c2" (EMPID in number) return varchar2 is
  temp varchar2(64):='';
  Result varchar2(100):='';
  CURSOR m_c IS SELECT CODE FROM EMPCODE WHERE  EMPID = EMPID;
  m_r_c EMPCODE.CODE%type;
begin
     open m_c;
     LOOP
         FETCH m_c INTO m_r_c;
    EXIT WHEN m_c % NOTFOUND;
    temp := temp || m_r_c || ', ';
     END LOOP;     
     close m_c;
     Result:=temp;
     Result:=rtrim(Result,1);
  return Result;
end get_c2;CREATE OR REPLACE FUNCTION "get_c3" (EMPID in number) return varchar2 is
  temp varchar2(100):='';
  Result varchar2(1000):='';
  CURSOR m_c IS 
  SELECT 
  ROLE_NAME 
  FROM EMPROLE ER,ROLE R 
  WHERE ER.ROLEID=R.ROLEID AND EMPID = EMPID;
  m_r_c ROLE.ROLE_NAME%type;
begin
     open m_c;
     LOOP
         FETCH m_c INTO m_r_c;
    EXIT WHEN m_c % NOTFOUND;
    temp := temp || m_r_c || ', ';
     END LOOP;     
     close m_c;
     Result:=temp;
     Result:=rtrim(Result,1);
  return Result;
end get_c3;select distinct(e.empid), e.empid,  e.name,  e.age,  get_c2(code)  code,  get_c3(rolename)  rolename  
from  employee  e,  empcode  ec,  emprole  er,  role  r  
where  e.empid(+)  =  ec.empid  and  e.empid(+)  =  er.empid  and  er.roleid  =  r.roleid(+)