(新手)高难度多表查询

[hzfurongmu]

----dept表内容：
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
--emp表内容：
EMPNO ENAME SAL DEPTNO
7782 CLARK 2450 10
7839 KING 5000 10
7934 MILLER 1300 10
7369 SMITH 800 20
7566 JONES 2975 20
7788 SCOTT 3000 20
7876 ADAMS 1100 20
7902 FORD 3000 20
7499 ALLEN 1600 30
7521 WARD 1250 30
7654 MARTIN 1250 30
7698 BLAKE 2850 30
7844 TURNER 1500 30
7900 JAMES 950 30

想得到的查询结果为：
DEPTNO部门编码 DNAME部门名称 LOC位置 EMPNO雇员编码 ENAME雇员名字
10 ACCOUNTING NEW YORK [7782][7839][7934] [CLARK][KING][MILLER]
20 RESEARCH DALLAS [7369][7566][7788][7876][7902] [SMITH][JONES][SCOTT][ADAMS][FORD]
30 SALES CHICAGO [7499][7521][7654][7698][7844][7900] [ALLEN][WARD][MARTIN][BLAKE][TURNER][JAMES]
40 OPERATIONS BOSTON

说明：对emp表中的相同DEPTNO的雇员查询结果进入一个新记录的一个字段中，例如EMPNO雇员编码，（项目个数不固定），
其中emp表中的empno是唯一字段；dept表中deptno字段是唯一的；
对于dept表中有的字段，而emp表中没有的，在查询结果中也要显示出来。
--PS：在csdn中找答案很久了，没办法，麻烦各位了！

[oracledbalgtu]

10g的实现方法：
SELECT D.DEPTNO,
       D.DNAME,
       D.LOC,
       REPLACE('[' || REPLACE(WMSYS.WM_CONCAT(E.EMPNO), ',', '][') || ']',
               '[]',
               '') EMPNO,
       REPLACE('[' || REPLACE(WMSYS.WM_CONCAT(E.ENAME), ',', '][') || ']',
               '[]',
               '') ENAME
  FROM SCOTT.EMP E, SCOTT.DEPT D
 WHERE D.DEPTNO = E.DEPTNO(+)
 GROUP BY D.DEPTNO, D.DNAME, D.LOC;

[Quote=引用楼主 hzfurongmu 的帖子:]
----dept表内容：
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
--emp表内容：
EMPNO ENAME SAL DEPTNO
7782 CLARK 2450 10
7839 KING 5000 10
7934 MILLER 1300 10
7369 SMITH 800 20
7566 JONES 2975 20
7788 SCOTT 3000 20
7876 ADAMS 1100 20
7902 FORD 3000 20
7499 ALLEN 1600 30
7521 WARD 1250 30
7654 MARTIN 1250 30
76…
[/Quote]

[oracledbalgtu]

9i,8i实现方法:
CREATE OR REPLACE FUNCTION MY_CONCAT(P_DEPTNO  INT,
                                     P_SPLITBY VARCHAR2,
                                     P_COLNAME VARCHAR2) RETURN VARCHAR2 IS
  V_STR      VARCHAR2(2000) := '';
  R          SYS_REFCURSOR;
  V_TEMP_STR VARCHAR2(100);
BEGIN
  OPEN R FOR 'SELECT ' || P_COLNAME || ' FROM scott.emp WHERE deptno=' || P_DEPTNO;
  LOOP
    FETCH R
      INTO V_TEMP_STR;
    EXIT WHEN R%NOTFOUND;
    IF LENGTH(V_STR) > 0 THEN
      V_STR := V_STR || P_SPLITBY || V_TEMP_STR;
    ELSE
      V_STR := V_TEMP_STR;
    END IF;
  END LOOP;
  CLOSE R;
  RETURN(V_STR);
END MY_CONCAT;
/

SELECT D.DEPTNO,
       D.DNAME,
       D.LOC,
       REPLACE('[' || REPLACE(MY_CONCAT(d.deptno,'][','empno') , ',', '][') || ']',
               '[]',
               '') EMPNO,
       REPLACE('[' || REPLACE(MY_CONCAT(d.deptno,'][','ename'), ',', '][') || ']',
               '[]',
               '') ENAME
  FROM SCOTT.EMP E, SCOTT.DEPT D
 WHERE D.DEPTNO = E.DEPTNO(+)
 GROUP BY D.DEPTNO, D.DNAME, D.LOC
 ORDER BY 1;

输出:
10	ACCOUNTING	NEW YORK	[7782][7839][7934]	[CLARK][KING][MILLER]
20	RESEARCH	DALLAS	[7369][7566][7902][2222]	[SMITH][JONES][FORD][lgt]
30	SALES	CHICAGO	[7499][7521][7654][7698][7844][7900]	[ALLEN][WARD][MARTIN][BLAKE][TURNER][JAMES]
40	OPERATIONS	BOSTON		

[Quote=引用 1 楼 oracledbalgtu 的回复:]
SQL code
10g的实现方法：
SELECT D.DEPTNO,
D.DNAME,
D.LOC,
REPLACE('[' || REPLACE(WMSYS.WM_CONCAT(E.EMPNO), ',', '][') || ']',
'[]',
'') EMPNO,
REPLACE('[' || REPLACE(WMSYS.WM_CONCAT(E.ENAME), ',', '][') || ']',
'[]',
'') ENAME
FROM SCOTT.EMP E, SCOTT.DEPT D
WHERE D.DEPTNO = E.DEPTNO(+)
GROUP …
[/Quote]

[hzfurongmu]

太感谢了！
我开始看10g的实在没有办法，找了资料，那个函数好像9i没有办法用的。