DB2 實用 SQL 範例大全：35 個常見查詢、統計、更新、連接場景

前言

本文整理 35 個 DB2 實務常見 SQL 範例，涵蓋條件查詢、分組統計、多表連接、子查詢、外部連接、CASE 表達式、日期轉換、資料更新等核心場景，供開發人員快速參考與改寫。

範例清單

1. 員工明細 + 空值處理 + 排序
   SELECT emp_no, emp_name, dept, COALESCE(CHAR(birthday, ISO), '日期不詳') AS birthday FROM employee ORDER BY dept;
2. 同部門同事查詢 (子查詢)
   SELECT emp_name, dept, title FROM employee WHERE emp_name <> '喻自強' AND dept IN (SELECT dept FROM employee WHERE emp_name = '喻自強');
3. 部門薪資總和
   SELECT dept, SUM(salary) FROM employee GROUP BY dept;
4. 特定商品銷售明細 (內連接)
   SELECT a.prod_id, a.qty, a.unit_price, a.qty*a.unit_price AS tot_price FROM sale_item a JOIN product b ON a.prod_id=b.prod_id WHERE b.prod_name='14吋顯示器';
5. 產品銷售彙總
   SELECT prod_id, SUM(qty) AS tot_qty, SUM(qty*unit_price) AS tot_amt FROM sale_item GROUP BY prod_id;
6. 依年份統計客戶訂單總額
   SELECT cust_id, SUM(tot_amt) FROM sales WHERE YEAR(order_date)=1996 GROUP BY cust_id;
7. 有銷售記錄客戶彙總
   SELECT a.cust_id, a.cust_name, SUM(b.tot_amt) FROM customer a JOIN sales b ON a.cust_id=b.cust_id GROUP BY a.cust_id, a.cust_name;
8. 特定年份有銷售記錄客戶
   SELECT a.cust_id, a.cust_name, SUM(b.tot_amt) FROM customer a JOIN sales b ON a.cust_id=b.cust_id WHERE YEAR(b.order_date)=1997 GROUP BY a.cust_id, a.cust_name;
9. 單筆最大訂單
   SELECT order_no, cust_id, sale_id, tot_amt FROM sales WHERE tot_amt = (SELECT MAX(tot_amt) FROM sales);
10. 至少 3 筆訂單的業務員
    SELECT emp_name, order_date FROM employee a JOIN sales b ON a.emp_no=b.sale_id WHERE a.emp_no IN (SELECT sale_id FROM sales GROUP BY sale_id HAVING COUNT(*) >= 3) ORDER BY emp_name;
11. 無訂單客戶 (NOT EXISTS)
    SELECT cust_name FROM customer a WHERE NOT EXISTS (SELECT 1 FROM sales b WHERE a.cust_id=b.cust_id);
12. 左外部連接：所有客戶含訂單明細
    SELECT a.cust_id, a.cust_name, DATE(b.order_date) AS order_date, b.tot_amt FROM customer a LEFT JOIN sales b ON a.cust_id=b.cust_id ORDER BY a.cust_id, b.tot_amt DESC;
13. 多表連接 + CASE 性別轉換
    SELECT a.emp_name, CASE a.sex WHEN 'M' THEN '男' WHEN 'F' THEN '女' ELSE '未知' END AS sex, DATE(c.order_date) AS order_date, c.qty, c.qty*c.unit_price AS amt FROM employee a JOIN sales b ON a.emp_no=b.sale_id JOIN sale_item c ON b.order_no=c.order_no JOIN product d ON c.prod_id=d.prod_id WHERE d.prod_name='16M DRAM';
14. 左外部連接保留所有業務員
    SELECT a.emp_no, a.emp_name, CASE a.sex WHEN 'M' THEN '男' WHEN 'F' THEN '女' ELSE '未知' END AS sex, d.prod_name, DATE(c.order_date) AS order_date, c.qty, c.qty*c.unit_price AS amt FROM employee a LEFT JOIN sales b ON a.emp_no=b.sale_id JOIN sale_item c ON b.order_no=c.order_no JOIN product d ON c.prod_id=d.prod_id;
15. 銷售額最高客戶 (巢狀子查詢)
    SELECT a.cust_name, d.cust_sum FROM customer a JOIN (SELECT cust_id, cust_sum FROM (SELECT cust_id, SUM(tot_amt) AS cust_sum FROM sales GROUP BY cust_id) b WHERE b.cust_sum = (SELECT MAX(cust_sum) FROM (SELECT cust_id, SUM(tot_amt) AS cust_sum FROM sales GROUP BY cust_id) c)) d ON a.cust_id=d.cust_id;
16. 銷售總額 < 1000 的業務員
    SELECT a.emp_no, a.emp_name, d.sale_sum FROM employee a JOIN (SELECT sale_id, sale_sum FROM (SELECT sale_id, SUM(tot_amt) AS sale_sum FROM sales GROUP BY sale_id) b WHERE b.sale_sum < 1000) d ON a.emp_no=d.sale_id;
17. 購買 ≥ 3 種商品的客戶明細
    SELECT a.cust_id, a.cust_name, b.prod_id, b.prod_name, c.qty, c.qty*c.unit_price FROM customer a JOIN sales c ON a.cust_id=c.cust_id JOIN sale_item c ON c.order_no=sale_item.order_no JOIN product b ON sale_item.prod_id=b.prod_id WHERE a.cust_id IN (SELECT cust_id FROM (SELECT cust_id, COUNT(DISTINCT prod_id) AS cnt FROM sales JOIN sale_item ON sales.order_no=sale_item.order_no GROUP BY cust_id HAVING COUNT(DISTINCT prod_id) >= 3) t);
18. 關係除法：銷售商品包含特定客戶所有商品的客戶
    SELECT a.cust_id, a.cust_name, d.prod_id, d.prod_name, d.qty, d.qty*d.unit_price FROM customer a JOIN sales c ON a.cust_id=c.cust_id JOIN sale_item d ON c.order_no=d.order_no JOIN product b ON d.prod_id=b.prod_id WHERE NOT EXISTS (SELECT 1 FROM customer x JOIN sales e ON x.cust_id=e.cust_id JOIN sale_item f ON e.order_no=f.order_no WHERE x.cust_name='世界技術開發公司' AND NOT EXISTS (SELECT 1 FROM sale_item g JOIN sales h ON g.order_no=h.order_no WHERE g.prod_id=f.prod_id AND h.cust_id=a.cust_id));
19. 姓劉員工
    SELECT emp_no, emp_name, dept, salary FROM employee WHERE emp_name LIKE '劉%';
20. 訂單金額 > 2000 的客戶
    SELECT DISTINCT cust_id FROM sales WHERE tot_amt > 2000;
21. 薪資 4000-6000 員工人數
    SELECT COUNT(*) FROM employee WHERE salary BETWEEN 4000 AND 6000;
22. 上海員工部門平均薪資
    SELECT dept, AVG(salary) FROM employee WHERE addr LIKE '上海市%' GROUP BY dept;
23. 更新地址：上海→北京
    UPDATE employee SET addr = REPLACE(addr, '上海市', '北京市') WHERE addr LIKE '上海市%';
24. 業務/會計部女員工
    SELECT emp_no, emp_name, dept FROM employee WHERE sex='F' AND dept IN ('業務','會計');
25. 產品銷售總額降序
    SELECT prod_id, SUM(qty*unit_price) FROM sale_item GROUP BY prod_id ORDER BY 2 DESC;
26. 客戶編號範圍查詢
    SELECT cust_id, cust_name, addr FROM customer WHERE cust_id BETWEEN 'C0001' AND 'C0004';
27. 銷售商品種類數
    SELECT COUNT(DISTINCT prod_id) FROM sale_item;
28. 業務部薪資調漲 3%
    UPDATE employee SET salary = salary * 1.03 WHERE dept = '業務';
29. 最低薪資員工
    SELECT * FROM employee WHERE salary = (SELECT MIN(salary) FROM employee);
30. JOIN 查詢特定客戶訂單
    SELECT a.cust_name, b.tot_amt, DATE(b.order_date) AS order_date, a.tel_no FROM customer a JOIN sales b ON a.cust_id=b.cust_id WHERE a.cust_name = '客戶丙';
31. 訂單金額大於特定業務員特定日期所有訂單 (ALL 量詞)
    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;
32. 特定商品平均單價
    SELECT AVG(unit_price) FROM sale_item WHERE prod_id='P0001';
33. 女業務員接單明細
    SELECT sale_id, tot_amt FROM sales WHERE sale_id IN (SELECT emp_no FROM employee WHERE sex='F');
34. 同一天入職員工 (自連接)
    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;
35. 業績超過 232000 業務員 (HAVING)
    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);

DB2 語法備註

• 日期轉換：DATE(col)、YEAR(col)、CHAR(col, ISO) (YYYY-MM-DD)。
• 空值處理：COALESCE、IFNULL (相容)、NVL (相容)。
• 字串連接：|| 或 CONCAT。
• CASE 表達式支援簡單/搜尋兩種形式。

結語

這 35 個範例涵蓋了 OLTP 開發 80% 以上的常見查詢模式。建議收藏並在實際專案中依據資料模型調整表/欄位名稱直接套用。