目錄
列出薪金比 SMITH 或 ALLEN 多的所有員工的編號、姓名、部門名稱、領導姓名、部門人數，以及所在部門的平均工資、最高和最低工資
補充
spool
Oracle從入門到總裁:https://blog.csdn.net/weixin_67859959/article/details/135209645
列出薪金比 SMITH 或 ALLEN 多的所有員工的編號、姓名、部門名稱、領導姓名、部門人數，以及所在部門的平均工資、最高和最低工資
?確定要使用的數據表
emp 表：員工的編號、姓名
dept 表：部門名稱
emp 表：領導姓名
emp 表：統計信息
確定已知的關聯字段
員工與部門：emp.deptno=dept.deptno
員工與領導：emp.mgr=memp.empno
第一步：知道 SMITH 或 ALLEN 的薪金，這個查詢返回多行單列（WHERE 中使用）。
SQL> select sal2 from emp3 where ename in('SMITH','ALLEN');SAL
----------8001600第二步：應該比里面的任意一個多即可，但是要去掉這兩個員工
由于是多行單列子查詢，所以使用 >ANY 完成
SQL> select e.empno,e.ename,e.sal2 from emp e3 where e.sal>any(4 select sal5 from emp6 where ename in('SMITH','ALLEN'))7 and e.ename not in('SMITH','ALLEN');EMPNO ENAME SAL
---------- -------------------- ----------7839 KING 50007902 FORD 30007566 JONES 29757698 BLAKE 28507782 CLARK 24507844 TURNER 15007934 MILLER 13007654 MARTIN 12507521 WARD 12507900 JAMES 950已選擇 10 行。第三步：找到部門名稱
SQL> select e.empno,e.ename,e.sal,d.dname2 from emp e,dept d3 where e.sal>any(4 select sal5 from emp6 where ename in('SMITH','ALLEN'))7 and e.ename not in('SMITH','ALLEN')8 and e.deptno=d.deptno;EMPNO ENAME SAL DNAME
---------- -------------------- ---------- ----------------------------7839 KING 5000 ACCOUNTING7902 FORD 3000 RESEARCH7566 JONES 2975 RESEARCH7698 BLAKE 2850 SALES7782 CLARK 2450 ACCOUNTING7844 TURNER 1500 SALES7934 MILLER 1300 ACCOUNTING7521 WARD 1250 SALES7654 MARTIN 1250 SALES7900 JAMES 950 SALES第四步：找到領導信息
SQL> select e.empno,e.ename,e.sal,d.dname,m.ename2 from emp e,dept d,emp m3 where e.sal>any(4 select sal5 from emp6 where ename in('SMITH','ALLEN'))7 and e.ename not in('SMITH','ALLEN')8 and e.deptno=d.deptno9 and e.mgr=m.empno(+);EMPNO ENAME SAL DNAME ENAME
---------- -------------------- ---------- ---------------------------- --------------------7902 FORD 3000 RESEARCH JONES7844 TURNER 1500 SALES BLAKE7521 WARD 1250 SALES BLAKE7654 MARTIN 1250 SALES BLAKE7900 JAMES 950 SALES BLAKE7934 MILLER 1300 ACCOUNTING CLARK7566 JONES 2975 RESEARCH KING7698 BLAKE 2850 SALES KING7782 CLARK 2450 ACCOUNTING KING7839 KING 5000 ACCOUNTING已選擇 10 行。第五步：得到部門人數，以及部門平均工資、最高和最低工資
整個查詢里面不能夠直接使用GROUP BY，所以現在應該利用子查詢實現統計操作
SQL> select e.empno,e.ename,e.sal,d.dname,m.ename,temp.count,temp.avg,temp.max,temp.min2 from emp e,dept d,emp m,(3 select deptno dno,count(empno) count,avg(sal) avg,max(sal) max,min(sal) min4 from emp5 group by deptno) temp6 where e.sal>any(7 select sal8 from emp9 where ename in('SMITH','ALLEN'))10 and e.ename not in('SMITH','ALLEN')11 and e.deptno=d.deptno12 and e.mgr=m.empno(+)13 and d.deptno=temp.dno;EMPNO ENAME SAL DNAME ENAME COUNT AVG MAX MIN
---------- -------------------- ---------- ---------------------------- -------------------- ---------- ---------- ---------- ----------7902 FORD 3000 RESEARCH JONES 3 2258.33333 3000 8007844 TURNER 1500 SALES BLAKE 6 1566.66667 2850 9507654 MARTIN 1250 SALES BLAKE 6 1566.66667 2850 9507521 WARD 1250 SALES BLAKE 6 1566.66667 2850 9507900 JAMES 950 SALES BLAKE 6 1566.66667 2850 9507934 MILLER 1300 ACCOUNTING CLARK 3 2916.66667 5000 13007566 JONES 2975 RESEARCH KING 3 2258.33333 3000 8007698 BLAKE 2850 SALES KING 6 1566.66667 2850 9507782 CLARK 2450 ACCOUNTING KING 3 2916.66667 5000 13007839 KING 5000 ACCOUNTING 3 2916.66667 5000 1300已選擇 10 行。補充
spool
在數據庫實際操作中，需要將查詢結果輸出到指定文件中，此時可以使用 spool 命令?
spool filename [create][replace][append]|OFF|OUT其中
filename 為輸出的文件名稱，默認的擴展名為 LST，后面所跟參數作用如下
create 表示創建新的文件，是默認狀態
replace 表示替代已經存在的文件
append 表示把查詢內容附加到已經存在的文件中
OFF|OUT 表示關閉輸出
例如:
下面代碼將查詢結果輸出到 D 盤的 temp.ext 文件中?
SQL> spool d:\temp.txt
SQL> select empno,ename,job from emp;EMPNO ENAME JOB
---------- -------------------- ------------------7369 SMITH CLERK7499 ALLEN SALESMAN7521 WARD SALESMAN7566 JONES MANAGER7654 MARTIN SALESMAN7698 BLAKE MANAGER7782 CLARK MANAGER7839 KING PRESIDENT7844 TURNER SALESMAN7900 JAMES CLERK7902 FORD ANALYSTEMPNO ENAME JOB
---------- -------------------- ------------------7934 MILLER CLERK8989 HELLO已選擇 13 行。SQL> spool off此時,我的D盤就有了這個文件,內容如下?
