SQL语句之问(1)

Question

数据表：Oracle数据库默认scott用户的emp和dept两张表
SQL功能：查询各个部门最高工资员工的信息（deptno,dname,sal,empno,ename）注意!OPERATIONS部门没有员工，但必须在查询结果中显示该部门的信息

Answer 1

 
select e.ename,e.deptno from emp e 
 where e.deptno,e.sal 
 in (select e.deptno,max(e.sal) 
 from emp e group by e.deptno);

Answer 2

SQL> select * from emp;     EMPNO ENAME      JOB              SAL     DEPTNO
---------- ---------- --------- ---------- ----------
      7369 SMITH      CLERK            800         20
      7499 ALLEN      SALESMAN        1600         30
      7521 WARD       SALESMAN        1250         30
      7566 JONES      MANAGER         2975         20
      7654 MARTIN     SALESMAN        1250         30
      7698 BLAKE      MANAGER         2850         30
      7782 CLARK      MANAGER         2450         10
      7788 SCOTT      ANALYST         3000         20
      7839 KING       PRESIDENT       5000         10
      7844 TURNER     SALESMAN        1500         30
      7876 ADAMS      CLERK           1100         20
      7900 JAMES      CLERK            950         30
      7902 FORD       ANALYST         3000         20
      7934 MILLER     CLERK           1300         10                             SQL> select * from dept;    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
 

Answer 3

select A.deptno
     , A.dname
     , B.sal
     , C.empno
     , C.ename
from dept A
   , (select DEPTNO, max(SAL) as sal
      from emp
      group by DEPTNO) B
   , emp C
where A.DEPTNO = B.DEPTNO(+)
and   B.DEPTNO = C.DEPTNO
and   B.sal = C.sal;效率应该差点,慢慢优化.

Answer 4

select A.deptno
    , A.dname
    , B.sal
    , C.empno
    , C.ename
from dept A
  , (select DEPTNO, max(SAL) as sal
      from emp
      group by DEPTNO) B
  , emp C
where A.DEPTNO = B.DEPTNO(+)
and  B.DEPTNO = C.DEPTNO(+)
and  B.sal = C.sal(+);漏了..... 

Answer 5

使用右外连接方法一，使用分析函数 rank()SQL> SELECT d.deptno, d.dname, x.empno, x.ename, x.job, x.sal
  2    FROM (SELECT e.empno,
  3                 e.ename,
  4                 e.job,
  5                 e.sal,
  6                 e.deptno,
  7                 rank() OVER(PARTITION BY e.deptno ORDER BY e.sal DESC) rn
  8            FROM emp e) x
  9   RIGHT JOIN dept d ON x.deptno = d.deptno
 10   WHERE x.rn = 1 OR x.rn IS NULL
 11   ORDER BY d.deptno;DEPTNO DNAME                EMPNO ENAME                JOB                        SAL
------ -------------------- ----- -------------------- -------------------- ---------
    10 ACCOUNTING            7839 KING                 PRESIDENT                 5000
    20 RESEARCH              7788 SCOTT                ANALYST                   3000
    20 RESEARCH              7902 FORD                 ANALYST                   3000
    30 SALES                 7698 BLAKE                MANAGER                   2850
    40 OERATIONS                                                            
方法二，使用多列 IN 子查询SQL> SELECT d.deptno, d.dname, e.empno, e.ename, e.job, e.sal
  2    FROM emp e
  3   RIGHT JOIN dept d ON e.deptno = d.deptno
  4   WHERE (e.deptno, e.sal) IN
  5         (SELECT x.deptno, max(x.sal) FROM emp x GROUP BY x.deptno)
  6      OR e.deptno IS NULL
  7   ORDER BY d.deptno;DEPTNO DNAME                EMPNO ENAME                JOB                        SAL
------ -------------------- ----- -------------------- -------------------- ---------
    10 ACCOUNTING            7839 KING                 PRESIDENT                 5000
    20 RESEARCH              7788 SCOTT                ANALYST                   3000
    20 RESEARCH              7902 FORD                 ANALYST                   3000
    30 SALES                 7698 BLAKE                MANAGER                   2850
    40 OERATIONS                                                            

Answer 6

select d.deptno, d.dname, e.empno, e.ename,e.sal
from emp e,dept d 
where e.deptno(+)=d.deptno 
and 
((e.deptno,e.sal) in (select deptno, max(sal) from emp group by deptno)
   or e.deptno is NULL) ;