Oracle 多表關聯查詢實戰：部門統計與最低/最高薪資員工姓名單次 SQL 獲取技巧

Oracle 多表關聯查詢實戰：部門統計與最低/最高薪資員工姓名單次 SQL 獲取技巧

業務需求

查詢每個部門的：部門名稱、員工數、平均工資、最低薪資員工姓名、最高薪資員工姓名。基於 Scott 模式 emp、dept 表。

表結構

分步解析法（教學用）

步驟 1：部門聚合統計

SELECT deptno, COUNT(*), AVG(sal), MIN(sal) min_sal, MAX(sal) max_sal
FROM emp GROUP BY deptno;

步驟 2：關聯查最低薪資員工

SELECT e.ename
FROM emp e,
     (SELECT deptno, MIN(sal) min_sal FROM emp GROUP BY deptno) t
WHERE e.deptno = t.deptno AND e.sal = t.min_sal;

步驟 3：同理查最高薪資員工

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

單次 SQL 完美融合（關聯子查詢 + 雙表 JOIN）

SELECT d.dname,
       t.cnt        AS emp_count,
       t.avg_sal    AS avg_salary,
       t.min_sal    AS min_salary,
       t.max_sal    AS max_salary,
       e_low.ename  AS min_sal_emp,
       e_high.ename AS max_sal_emp
FROM dept d
JOIN (
    SELECT deptno,
           COUNT(*)     AS cnt,
           AVG(sal)     AS avg_sal,
           MIN(sal)     AS min_sal,
           MAX(sal)     AS max_sal
    FROM emp
    GROUP BY deptno
) t ON d.deptno = t.deptno
JOIN emp e_low  ON e_low.deptno  = t.deptno AND e_low.sal  = t.min_sal
JOIN emp e_high ON e_high.deptno = t.deptno AND e_high.sal = t.max_sal;

進階技巧與避坑

1. 同薪資多員工處理：若部門內多人並列最低/最高薪，上述 SQL 會產生笛卡爾積（行數乘積）。解決：
   • 取任意一個：ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal, empno) = 1
   • 聚合姓名：LISTAGG(ename, ',') WITHIN GROUP (ORDER BY empno) (11g+)
2. WITH 子句提升可讀性 (Oracle 9i+)：

   WITH dept_stats AS (
       SELECT deptno, COUNT(*) cnt, AVG(sal) avg_sal, MIN(sal) min_sal, MAX(sal) max_sal
       FROM emp GROUP BY deptno
   )
   SELECT d.dname, s.cnt, s.avg_sal, s.min_sal, s.max_sal,
          e1.ename min_emp, e2.ename max_emp
   FROM dept d
   JOIN dept_stats s ON d.deptno = s.deptno
   JOIN emp e1 ON e1.deptno = s.deptno AND e1.sal = s.min_sal
   JOIN emp e2 ON e2.deptno = s.deptno AND e2.sal = s.max_sal;

3. 性能優化：emp(deptno, sal) 複合索引支持聚合與關聯；大表考慮物化視圖預聚合。

核心模式總結

「聚合統計子查詢」+「雙向關聯主表取詳情」是處理「分組統計 + 極值對應業務字段」的通用模式，適用於：部門最高銷售顧客、班級最高分學生、倉庫最低庫存商品等場景。