SQL 高手精萃：12 條實戰精妙 SQL 陳述式範例

1. 複製表結構 (不含資料)

SELECT * INTO b FROM a WHERE 1 <> 1;

2. 複製表資料 (結構已存在)

INSERT INTO b (a, b, c) SELECT d, e, f FROM a;

3. 顯示文章、發文者、最後回覆時間

SELECT a.title, a.username, b.adddate
FROM table a,
     (SELECT MAX(adddate) AS adddate FROM table WHERE table.title = a.title) b;

4. 左外部連接查詢

SELECT a.a, a.b, a.c, b.c, b.d, b.f
FROM a LEFT OUTER JOIN b ON a.a = b.c;

5. 行程提醒：提前 5 分鐘

SELECT * FROM 日程安排
WHERE DATEDIFF('minute', f開始時間, GETDATE()) > 5;

6. 刪除主表中副表已無對應的資料

DELETE FROM info
WHERE NOT EXISTS (SELECT * FROM infobz WHERE info.infid = infobz.infid);

7. 複雜多表關聯查詢 (Oracle 語法)

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1 A,
     (SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE AS PREV_UPD_DATE
      FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
            FROM TABLE2
            WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE,'YYYY/MM')) X,
           (SELECT NUM, UPD_DATE, STOCK_ONHAND
            FROM TABLE2
            WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM')||'/01','YYYY/MM/DD')-1,'YYYY/MM')) Y
      WHERE X.NUM = Y.NUM (+)
        AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND) B
WHERE A.NUM = B.NUM;

8. 學生資料篩選 (動態 SQL 片段)

SELECT * FROM studentinfo
WHERE NOT EXISTS (SELECT * FROM student WHERE studentinfo.id = student.id)
  AND 系名稱 = '...' AND 專業名稱 = '...'
ORDER BY 性別, 生源地, 高考總成績;

9. 一年電話費統計 (行轉欄，Oracle DECODE)

SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate,'yyyy') AS telyear,
       SUM(DECODE(TO_CHAR(a.telfeedate,'mm'),'01',a.factration)) AS JAN,
       SUM(DECODE(TO_CHAR(a.telfeedate,'mm'),'02',a.factration)) AS FEB,
       ... -- 03~12 月同理
       SUM(DECODE(TO_CHAR(a.telfeedate,'mm'),'12',a.factration)) AS DEC
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
      FROM TELFEESTAND a, TELFEE b WHERE a.tel = b.telfax) a
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate,'yyyy');

10. 四表聯結查詢

SELECT * FROM a
LEFT INNER JOIN b ON a.a = b.b
RIGHT INNER JOIN c ON a.a = c.c
INNER JOIN d ON a.a = d.d
WHERE ...;

11. 取得表中最小未使用的 ID 號

SELECT (CASE WHEN EXISTS (SELECT * FROM Handle b WHERE b.HandleID = 1)
             THEN MIN(HandleID) + 1 ELSE 1 END) AS HandleID
FROM Handle
WHERE HandleID NOT IN (SELECT a.HandleID - 1 FROM Handle a);

涵蓋 DDL、DML、多表連接、子查詢、行轉欄、缺口查詢等高頻實戰場景。**理解邏輯優於死記語法**，舉一反三靈活應用。