Oracle 常見 SQL 查詢實戰解析：從部門薪資統計到進階分析技巧

前言

在 Oracle 資料庫日常開發與維護中，掌握高效的 SQL 查寫技巧至關重要。本文整理了多個經典實戰場景，涵蓋分組統計、子查詢應用、集合運算、層級查詢等核心知識點，並附帶詳細代碼解析，幫助開發者快速提升 SQL 能力。

一、部門薪資相關查詢

1. 查詢各部門薪資最高的員工

使用內連結搭配子查詢，先按部門分組求最大薪資，再關聯回原表獲取員工詳情：

SELECT e.ename, e.sal
FROM emp e
JOIN (
    SELECT deptno, MAX(sal) AS max_sal
    FROM emp
    GROUP BY deptno
) t ON e.deptno = t.deptno AND e.sal = t.max_sal;

2. 查詢各部門平均薪資對應的薪資等級

結合 salgrade 薪資等級表，利用 BETWEEN 進行範圍匹配：

SELECT t.deptno, t.avg_sal, s.grade
FROM (
    SELECT deptno, AVG(sal) AS avg_sal
    FROM emp
    GROUP BY deptno
) t
JOIN salgrade s ON t.avg_sal BETWEEN s.losal AND s.hisal;

3. 計算各部門員工薪資等級的平均值

先將員工薪資映射到等級，再按部門分組求平均：

SELECT deptno, AVG(grade)
FROM (
    SELECT e.deptno, s.grade
    FROM emp e
    JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
)
GROUP BY deptno;

二、管理者與薪資極值查詢

4. 找出所有擔任管理者的員工

利用 IN 子查詢，將 mgr 欄位去重後與 empno 比對：

SELECT ename
FROM emp
WHERE empno IN (SELECT DISTINCT mgr FROM emp);

5. 不使用聚合函數求最高薪資

通過自連結找出不存在更高薪資的記錄：

SELECT sal
FROM emp e1
WHERE NOT EXISTS (
    SELECT 1 FROM emp e2 WHERE e2.sal > e1.sal
);

6. 使用聚合函數求最高薪資

最直接的寫法：

SELECT MAX(sal) FROM emp;

三、部門平均薪資進階分析

7. 查詢平均薪資最高的部門編號

巢狀子查詢層層過濾：

SELECT deptno, avg_sal
FROM (
    SELECT deptno, AVG(sal) AS avg_sal
    FROM emp
    GROUP BY deptno
) t
WHERE avg_sal = (
    SELECT MAX(avg_sal)
    FROM (
        SELECT AVG(sal) AS avg_sal
        FROM emp
        GROUP BY deptno
    )
);

8. 聚合函數嵌套寫法簡化

Oracle 允許在 HAVING 或子查詢中直接嵌套聚合：

SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
HAVING AVG(sal) = (
    SELECT MAX(AVG(sal))
    FROM emp
    GROUP BY deptno
);

9. 查詢平均薪資最高部門的名稱

多表連結，逐層定位：

SELECT d.dname
FROM dept d
WHERE d.deptno = (
    SELECT deptno
    FROM (
        SELECT deptno, AVG(sal) AS avg_sal
        FROM emp
        GROUP BY deptno
    ) t
    WHERE avg_sal = (
        SELECT MAX(avg_sal)
        FROM (
            SELECT AVG(sal) AS avg_sal
            FROM emp
            GROUP BY deptno
        )
    )
);

10. 查詢平均薪資等級最低的部門名稱

結合薪資等級表，尋找等級最小的部門：

SELECT d.dname, t.avg_sal, s.grade
FROM (
    SELECT deptno, AVG(sal) AS avg_sal
    FROM emp
    GROUP BY deptno
) t
JOIN salgrade s ON t.avg_sal BETWEEN s.losal AND s.hisal
JOIN dept d ON t.deptno = d.deptno
WHERE s.grade = (
    SELECT MIN(grade)
    FROM (
        SELECT AVG(sal) AS avg_sal
        FROM emp
        GROUP BY deptno
    ) t2
    JOIN salgrade s2 ON t2.avg_sal BETWEEN s2.losal AND s2.hisal
);

四、最小平均薪資綜合查詢

11. 單純求最小平均薪資值

SELECT MIN(avg_sal)
FROM (
    SELECT AVG(sal) AS avg_sal
    FROM emp
    GROUP BY deptno
);

12. 求最小平均薪資及對應部門編號

SELECT avg_sal, deptno
FROM (
    SELECT AVG(sal) AS avg_sal, deptno
    FROM emp
    GROUP BY deptno
) t
WHERE avg_sal = (
    SELECT MIN(avg_sal)
    FROM (
        SELECT AVG(sal) AS avg_sal
        FROM emp
        GROUP BY deptno
    )
);

13. 含薪資等級的最小平均薪資查詢

SELECT t.avg_sal, t.deptno, s.grade
FROM (
    SELECT AVG(sal) AS avg_sal, deptno
    FROM emp
    GROUP BY deptno
) t
JOIN salgrade s ON t.avg_sal BETWEEN s.losal AND s.hisal
WHERE t.avg_sal = (
    SELECT MIN(avg_sal)
    FROM (
        SELECT AVG(sal) AS avg_sal
        FROM emp
        GROUP BY deptno
    )
);

14. 完整版：部門名、平均薪資、部門號、薪資等級

SELECT d.dname, t.avg_sal, t.deptno, s.grade
FROM (
    SELECT AVG(sal) AS avg_sal, deptno
    FROM emp
    GROUP BY deptno
) t
JOIN salgrade s ON t.avg_sal BETWEEN s.losal AND s.hisal
JOIN dept d ON t.deptno = d.deptno
WHERE t.avg_sal = (
    SELECT MIN(avg_sal)
    FROM (
        SELECT AVG(sal) AS avg_sal
        FROM emp
        GROUP BY deptno
    )
);

15. 替代寫法：按薪資等級直接過濾

SELECT t1.deptno, t1.avg_sal, t1.grade, d.dname
FROM (
    SELECT t.deptno, t.avg_sal, s.grade
    FROM (
        SELECT deptno, AVG(sal) AS avg_sal
        FROM emp
        GROUP BY deptno
    ) t
    JOIN salgrade s ON t.avg_sal BETWEEN s.losal AND s.hisal
) t1
JOIN dept d ON t1.deptno = d.deptno
WHERE t1.grade = (
    SELECT MIN(grade)
    FROM (
        SELECT t.deptno, t.avg_sal, s.grade
        FROM (
            SELECT deptno, AVG(sal) AS avg_sal
            FROM emp
            GROUP BY deptno
        ) t
        JOIN salgrade s ON t.avg_sal BETWEEN s.losal AND s.hisal
    )
);

五、權限授予與視圖創建

16. 以 SYSDBA 身份授予建表與建視圖權限

CONN sys/sys AS SYSDBA;
GRANT CREATE TABLE, CREATE VIEW TO scott;

總結

本文匯總了 Oracle 開發中高頻使用的查詢模式，從基礎的分組統計到複雜的多層巢狀子查詢，再到權限管理實操。掌握這些範本並理解其邏輯，能顯著提升處理業務數據的效率。實際應用中，建議結合執行計劃（EXPLAIN PLAN）優化大數據量場景下的查詢性能。