SQL 實戰技巧大全：複製表、跨表刪除、外部聯接、排程提醒等精妙語句

收錄多個實戰常用、高效率的 SQL 範例，涵蓋結構複製、資料遷移、複雜查詢、維護操作等場景。

1. 僅複製表結構（不含資料）

SELECT * INTO b FROM a WHERE 1 <> 1

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) 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, 開始時間, GETDATE()) > 5;

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

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

7. 複雜查詢：同表不同月份庫存對帳

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1 A,
     (SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE 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. 多條件篩選學生資料（不存在於另一表）

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

善用 NOT EXISTS、LEFT JOIN、子查詢與日期函數，能優雅解決多數資料處理難題。