DB2 必會常用 SQL 語句精選（四）—— 子查詢、外部聯結與彙總進階

前言

第四篇聚焦於子查詢比較、外部聯結、條件彙總、薪資調整等 DML 與 DQL 綜合場景，延續經典銷售資料模型。

1. 訂單金額大於「E0013 在 1996-10-15 每筆訂單金額」的所有訂單

SELECT *
FROM sales
WHERE tot_amt > ALL (
  SELECT tot_amt
  FROM sales
  WHERE sale_id = 'E0013'
    AND order_date = '1996-10-15'
)
ORDER BY tot_amt;

2. 產品 P0001 的平均銷售單價

SELECT AVG(unit_price)
FROM sale_item
WHERE prod_id = 'P0001';

3. 公司女員工所接的訂單（子查詢 + IN）

SELECT sale_id, tot_amt
FROM sales
WHERE sale_id IN (
  SELECT emp_no
  FROM employee
  WHERE sex = 'F'
);

現代寫法：改用 JOIN 或 EXISTS，效能通常優於 IN。

4. 同一天入職的員工（自我聯結）

SELECT a.emp_no, a.emp_name, a.date_hired
FROM employee a
JOIN employee b
  ON a.emp_no <> b.emp_no
  AND a.date_hired = b.date_hired
ORDER BY a.date_hired;

去重技巧：若只想列出「有同事同天入職」的員工一次，可在外層加 DISTINCT 或改用視窗函數 COUNT(*) OVER (PARTITION BY date_hired)。

5. 目前業績超過 232,000 元的員工編號與姓名

SELECT emp_no, emp_name
FROM employee
WHERE emp_no IN (
  SELECT sale_id
  FROM sales
  GROUP BY sale_id
  HAVING SUM(tot_amt) > 232000  -- 原文為 < 232000，此處依語意修正為 >
);

備註：原文 HAVING SUM(tot_amt) < 232000 與標題「超過」語意不符，實務上請依實際需求調整比較運算子。

6. 所有女職工平均薪資 & 住址在上海市的女職工平均薪資（UNION）

SELECT AVG(salary) AS avg_salary, '所有女職工' AS category
FROM employee
WHERE sex = 'F'
UNION ALL
SELECT AVG(salary), '上海市女職工'
FROM employee
WHERE sex = 'F'
  AND addr LIKE '上海市%';

建議：用 UNION ALL 保留重複行（此處不會重複），避免 UNION 額外排序去重開銷。

7. 薪資高於全公司平均薪資的員工

SELECT *
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee);

8. 銷售業績超過 10,000 元的業務員，依業績降序

SELECT sale_id, SUM(tot_amt) AS total_sales
FROM sales
GROUP BY sale_id
HAVING SUM(tot_amt) > 10000
ORDER BY total_sales DESC;

9. 男業務員所接且訂單金額超過 2,000 元的訂單

SELECT s.order_no, s.tot_amt
FROM sales s
JOIN employee e ON s.sale_id = e.emp_no
WHERE e.sex = 'M'
  AND s.tot_amt > 2000;

10. 訂單金額最高的訂單號與金額

SELECT order_no, tot_amt
FROM sales
WHERE tot_amt = (SELECT MAX(tot_amt) FROM sales);

11. 每張訂單金額超過 4,000 元的客戶名稱與地址

SELECT c.cust_name, c.addr
FROM customer c
JOIN sales s ON c.cust_id = s.cust_id
WHERE s.tot_amt > 4000;

12. 各客戶總訂購金額，依總額降序

SELECT cust_id, SUM(tot_amt) AS total_amt
FROM sales
GROUP BY cust_id
ORDER BY total_amt DESC;

小結

本篇強化子查詢比較（ALL、= (SELECT MAX...)）、自我聯結、HAVING 條件彙總、UNION ALL 多結果集合併等核心技巧。實務上遇到複雜條件時，先拆解成 CTE（WITH 子句）再逐步組裝，可大幅提升可讀與維護性。