您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 多表查詢實戰範例集：從基礎連接到進階分析函數

編輯：Oracle 資料庫基礎

多表查詢是 SQL 核心技能。本文精選 19 個經典場景，涵蓋**內連接、外連接、自連接、子查詢、聚合分組、集合運算、相關子查詢**等模式，每例附帶業務語意、SQL 寫法與結果解析，作為開發參考與面試複習資料。範例基於經典 SCOTT.EMP/DEPT 架構。

資料準備

-- EMP: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
-- DEPT: DEPTNO, DNAME, LOC

範例集

1. 列出至少有一個員工的部門 (EXISTS/IN)

SELECT deptno, dname, loc
FROM   dept
WHERE  deptno IN (SELECT deptno FROM emp);
-- 或 EXISTS 寫法，效能通常更佳
SELECT deptno, dname, loc
FROM   dept d
WHERE  EXISTS (SELECT 1 FROM emp e WHERE e.deptno = d.deptno);

2. 薪資高於 SMITH 的員工 (單值子查詢)

SELECT * FROM emp
WHERE  sal > (SELECT sal FROM emp WHERE ename = 'SMITH');

3. 員工與其直屬主管姓名 (自連接)

SELECT e.ename AS emp_name, m.ename AS mgr_name
FROM   emp e
JOIN   emp m ON e.mgr = m.empno;

4. 入職早於主管的員工 (自連接 + 日期比較)

SELECT e.ename, e.hiredate AS emp_hire, m.ename, m.hiredate AS mgr_hire
FROM   emp e
JOIN   emp m ON e.mgr = m.empno
WHERE  e.hiredate < m.hiredate;

5. 列出所有部門及員工，含無人部門 (左外連接)

-- Oracle 舊語法 (+)
SELECT d.dname, e.*
FROM   emp e, dept d
WHERE  e.deptno(+) = d.deptno;

-- ANSI SQL-92 標準 (推薦)
SELECT d.dname, e.*
FROM   dept d
LEFT JOIN emp e ON e.deptno = d.deptno;

6. 所有 CLERK 的姓名與部門名 (多表連接 + 過濾)

SELECT e.ename, d.dname
FROM   emp e
JOIN   dept d ON e.deptno = d.deptno
WHERE  e.job = 'CLERK';

7. 最低薪資超過 1500 的職位類別 (分組 + HAVING)

SELECT job
FROM   emp
GROUP  BY job
HAVING MIN(sal) > 1500;

8. SALES 部門員工 (子查詢找部門編號)

SELECT ename
FROM   emp
WHERE  deptno = (SELECT deptno FROM dept WHERE dname = 'SALES');

9. 薪資高於公司平均薪資的員工 (標量子查詢)

SELECT *
FROM   emp
WHERE  sal > (SELECT AVG(sal) FROM emp);

10. 與 SCOTT 同職位的員工 (IN 子查詢)

SELECT *
FROM   emp
WHERE  job IN (SELECT job FROM emp WHERE ename = 'SCOTT');

11. 薪資等於 30 部門任一員工薪資的人 (IN 多值子查詢)

SELECT ename, sal
FROM   emp
WHERE  sal IN (SELECT sal FROM emp WHERE deptno = 30);

12. 薪資高於 30 部門最高薪資的人 (標量子查詢 + 聚合)

SELECT ename, sal
FROM   emp
WHERE  sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);

13. 各部門人數、平均薪資、平均服務年限 (聚合)

SELECT d.dname,
       COUNT(e.empno) AS emp_cnt,
       ROUND(AVG(e.sal),2) AS avg_sal,
       ROUND(AVG(MONTHS_BETWEEN(SYSDATE, e.hiredate)/12),1) AS avg_years
FROM   dept d
LEFT JOIN emp e ON e.deptno = d.deptno
GROUP  BY d.dname;

14. 所有員工姓名、部門、總薪資 (含佣金)

SELECT e.ename, d.dname, (NVL(e.comm,0) + e.sal) AS total_pay
FROM   emp e
JOIN   dept d ON e.deptno = d.deptno;

15. 同職位不同部門的員工組合 (自連接 + 不等值)

SELECT a.ename, a.deptno, b.ename, b.deptno, a.job
FROM   emp a
JOIN   emp b ON a.job = b.job AND a.deptno != b.deptno;

16. 所有部門明細與人數 (標量子查詢在 SELECT 列)

SELECT d.*, 
       (SELECT COUNT(*) FROM emp e WHERE e.deptno = d.deptno) AS emp_cnt
FROM   dept d;

17. 各職位最低總薪資 (聚合 + NVL)

SELECT job, MIN(NVL(comm,0) + sal) AS min_total_pay
FROM   emp
GROUP  BY job;

18. 各部門 MANAGER 最低薪資 (分組過濾)

SELECT deptno, MIN(sal) AS min_mgr_sal
FROM   emp
WHERE  job = 'MANAGER'
GROUP  BY deptno;

19. 所有員工年薪由低到高排序 (運算欄位排序)

SELECT ename, (NVL(comm,0) + sal) * 12 AS annual_sal
FROM   emp
ORDER  BY annual_sal ASC;  -- 或 ORDER BY 2 ASC

進階擴充：分析函數重寫範例 13/16

-- 13 使用窗口函數避免重複掃描
SELECT DISTINCT
       d.dname,
       COUNT(e.empno) OVER (PARTITION BY d.deptno) AS emp_cnt,
       ROUND(AVG(e.sal) OVER (PARTITION BY d.deptno),2) AS avg_sal
FROM   dept d
LEFT JOIN emp e ON e.deptno = d.deptno;

-- 16 使用窗口函數
SELECT DISTINCT
       d.*,
       COUNT(e.empno) OVER (PARTITION BY d.deptno) AS emp_cnt
FROM   dept d
LEFT JOIN emp e ON e.deptno = d.deptno;