DB2 必會常用 SQL 語句精選（五）—— 進階查詢與統計實戰

前言

本系列第五篇，繼續整理實務上高頻出現的 DB2 查詢模式，涵蓋群組統計、子查詢比較、集合運算、多表聯結等進階技巧。所有範例假設使用經典銷售資料模型：employee、customer、sales、sale_item、product。

1. 每位客戶各產品的總購量與加權平均單價

SELECT 
  a.cust_id,
  b.prod_id,
  SUM(b.qty) AS total_qty,
  SUM(b.qty * b.unit_price) / SUM(b.qty) AS avg_unit_price
FROM sales a
JOIN sale_item b ON a.order_no = b.order_no
GROUP BY a.cust_id, b.prod_id
ORDER BY a.cust_id, b.prod_id;

重點：加權平均單價 = 總金額 / 總數量，避免單純 AVG(unit_price) 造成誤差。

2. 訂購 3 種以上不同產品的訂單編號

SELECT order_no
FROM sale_item
GROUP BY order_no
HAVING COUNT(DISTINCT prod_id) > 3;

關鍵：用 COUNT(DISTINCT prod_id) 計算「種類數」而非「明細行數」。

3. 訂購產品包含「訂單 3」所有產品的訂單（關係除法）

SELECT DISTINCT a.order_no
FROM sale_item a
WHERE a.order_no <> '3'
  AND NOT EXISTS (
    SELECT 1 
    FROM sale_item b 
    WHERE b.order_no = '3'
      AND NOT EXISTS (
        SELECT 1 
        FROM sale_item c 
        WHERE c.order_no = a.order_no 
          AND c.prod_id = b.prod_id
      )
  );

邏輯：雙重 NOT EXISTS 實作「集合包含」—— 找出不存在「訂單 3 有但該訂單沒有」的產品。

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

SELECT sale_id, tot_amt
FROM sales
WHERE tot_amt > ALL (
  SELECT tot_amt 
  FROM sales 
  WHERE sale_id = 'E0013' 
    AND order_date = '1996-11-10'
);

技巧：> ALL (子查詢) 等同於「大於子查詢結果的最大值」，比 > (SELECT MAX(...)) 更具語意。

5. 從未承接業務的員工資料

SELECT *
FROM employee a
WHERE NOT EXISTS (
  SELECT 1 
  FROM sales b 
  WHERE a.emp_no = b.sale_id
);

替代寫法：LEFT JOIN sales ... WHERE sales.sale_id IS NULL 效能通常更好。

6. 來自上海市客戶的姓名、電話、訂單號與金額

SELECT a.cust_name, a.tel_no, b.order_no, b.tot_amt
FROM customer a
JOIN sales b ON a.cust_id = b.cust_id
WHERE a.addr = '上海市';

7. 各業務員每月業績，依業務員編號、月份降序

SELECT 
  sale_id,
  MONTH(order_date) AS sales_month,
  SUM(tot_amt) AS monthly_total
FROM sales
GROUP BY sale_id, MONTH(order_date)
ORDER BY sale_id, sales_month DESC;

注意：DB2 用 MONTH(date) 擷取月份，Oracle 則用 EXTRACT(MONTH FROM date)。

8. 各產品總銷售數量與金額，顯示產品名稱，依產品編號排序

SELECT 
  a.prod_id,
  b.prod_name,
  SUM(a.qty) AS total_qty,
  SUM(a.qty * a.unit_price) AS total_amt
FROM sale_item a
JOIN product b ON a.prod_id = b.prod_id
GROUP BY a.prod_id, b.prod_name
ORDER BY a.prod_id;

9. 總訂購金額超過客戶 C0002 的客戶資料

SELECT cust_id, cust_name, addr
FROM customer
WHERE cust_id IN (
  SELECT cust_id
  FROM sales
  GROUP BY cust_id
  HAVING SUM(tot_amt) > (
    SELECT SUM(tot_amt) 
    FROM sales 
    WHERE cust_id = 'C0002'
  )
);

10. 業績最好的業務員編號、姓名與總銷售額

SELECT a.emp_no, a.emp_name, SUM(b.tot_amt) AS total_sales
FROM employee a
JOIN sales b ON a.emp_no = b.sale_id
GROUP BY a.emp_no, a.emp_name
HAVING SUM(b.tot_amt) = (
  SELECT MAX(sales_sum)
  FROM (
    SELECT SUM(tot_amt) AS sales_sum
    FROM sales
    GROUP BY sale_id
  ) t
);

11. 每位客戶各產品的明細清單（四表聯結）

SELECT 
  a.cust_id, a.cust_name,
  c.prod_id, d.prod_name,
  c.qty, c.unit_price
FROM customer a
JOIN sales b ON a.cust_id = b.cust_id
JOIN sale_item c ON b.order_no = c.order_no
JOIN product d ON c.prod_id = d.prod_id
ORDER BY a.cust_id, c.prod_id;

12. 各部門平均薪資，依平均薪資遞增排序

SELECT dept, AVG(salary) AS avg_salary
FROM employee
GROUP BY dept
ORDER BY avg_salary;

小結

本篇涵蓋加權平均、關係除法、ALL/ANY 子查詢、多表聯結彙總等實戰高頻模式。建議動手在測試庫跑一次，體會執行計畫差異，才能靈活應用於專案中。