获取正确数据的问题

Question

我创建了一个如下所示的查询，但我还没有找到将部门分组在一起的方法。这就是我需要帮助的部分。我想在一个列中获取部门编号，员工是另一列，然后是另一列中的同事。任何帮助都会很棒。

这就是我想要的样子

  DEPARTMENT    EMPLOYEE   COLLEAGUE
     10          CLARK         KING
     10          CLARK         MILLER
     10          KING          CLARK
     10          KING          MILLER
     10          MILLER        CLARK
     10          MILLER        KING
     20          ADAMS         FORD
     20          ADAMS         JONES
     20          ADAMS         SCOTT
     20          ADAMS         SMITH

这是我到目前为止所得到的，但不正确。国王的同事不是国王，而是克拉克和米勒。克拉克的同事不是克拉克而是国王和米勒。

    SQL> select ename AS Employee, deptno AS Department, ename AS Colleague from emp order by deptno asc;

   EMPLOYEE   DEPARTMENT COLLEAGUE
   ---------- ---------- ----------
   KING               10 KING
   CLARK              10 CLARK
   MILLER             10 MILLER
   ADAMS              20 ADAMS
   SCOTT              20 SCOTT
   SMITH              20 SMITH
   FORD               20 FORD
   JONES              20 JONES
   WARD               30 WARD
   JAMES              30 JAMES
   ALLEN              30 ALLEN

  EMPLOYEE   DEPARTMENT COLLEAGUE
  ---------- ---------- ----------
  MARTIN             30 MARTIN
  BLAKE              30 BLAKE
  TURNER             30 TURNER

 14 rows selected.

这是 dept 表

 SQL> select * from dept;

DEPTNO DNAME          LOC
---------- -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

这是 emp 表

 SQL> select ename, deptno, job from emp;

 ENAME          DEPTNO JOB
 ---------- ---------- ---------
 KING               10 PRESIDENT
 BLAKE              30 MANAGER
 CLARK              10 MANAGER
 JONES              20 MANAGER
 MARTIN             30 SALESMAN
 ALLEN              30 SALESMAN
 TURNER             30 SALESMAN
 JAMES              30 CLERK
 WARD               30 SALESMAN
 FORD               20 ANALYST
 SMITH              20 CLERK

 ENAME          DEPTNO JOB
 ---------- ---------- ---------
 SCOTT              20 ANALYST
 ADAMS              20 CLERK
 MILLER             10 CLERK

 14 rows selected.

Answer 1

听起来你想要类似的东西

SQL> ed
Wrote file afiedt.buf

  1  select deptno department,
  2         e1.ename employee,
  3         e2.ename colleague
  4    from emp e1 join emp e2 using (deptno)
  5   where e1.empno != e2.empno
  6*  order by deptno
SQL> /

DEPARTMENT EMPLOYEE   COLLEAGUE
---------- ---------- ----------
        10 MILLER     CLARK
        10 KING       CLARK
        10 MILLER     KING
        10 CLARK      MILLER
        10 KING       MILLER
        10 CLARK      KING
 <<additional results removed>>

Answer 2

 Select distinct d.dname, t1.employee, t2.employee college
 From emp t1
 inner join dept d on d.deptno = t1.deptno
 Inner join emp t2 on t2.deptno = t1.deptno and t1.employee <> t2.employee