實用 SQL 範例集：複製表、外部聯結、刪除重複、分頁查詢

常用 SQL 模式速查

1. 僅複製表結構

SELECT * INTO 新表 FROM 來源表 WHERE 1 <> 1

2. 複製資料（需目標表已存在）

INSERT INTO 目標表 (欄位1, 欄位2, 欄位3)
SELECT 欄位A, 欄位B, 欄位C FROM 來源表

3. 顯示文章、提交人與最新回覆時間

SELECT a.title, a.username, b.adddate
FROM table a
JOIN (SELECT MAX(adddate) AS adddate FROM table GROUP BY title) b
  ON a.title = b.title

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 1 FROM infobz WHERE info.infid = infobz.infid)

7. 當月庫存異動查詢（Oracle 風格）

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1 A
JOIN (
    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
    LEFT JOIN (
        SELECT NUM, UPD_DATE, STOCK_ONHAND
        FROM TABLE2
        WHERE TO_CHAR(UPD_DATE, 'YYYY/MM') = TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1), 'YYYY/MM')
    ) Y ON X.NUM = Y.NUM
    WHERE X.INBOUND_QTY + NVL(Y.STOCK_ONHAND, 0) <> X.STOCK_ONHAND
) B ON A.NUM = B.NUM

8. 學生資料篩選（動態參數）

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

使用提醒

以上範例展示核心模式，實際使用時請依資料庫平台 (SQL Server/Oracle/PostgreSQL) 調整函數與語法細節。