高手養成：經典實用 SQL 陳述式精選

從新手蛻變為高手，靈活運用 SQL 陳述式至關重要。本文收錄多個經典常用 SQL 範例供參考。

資料表操作

複製表結構 (不含資料)

SELECT * INTO b FROM a WHERE 1<>1

複製表資料

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

顯示文章、發文者與最新回覆時間

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

外部聯結查詢

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 分鐘

SELECT * FROM 行程表
WHERE DATEDIFF('minute', 開始時間, GETDATE()) > 5

刪除主表中副表無對應資料

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

進階查詢技巧

跨月份庫存比對 (Oracle 風格)

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1,
     (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

學生資料篩選 (動態 SQL)

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

電話費年度統計 (交叉表)

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,
       ...,
       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')

四表聯結

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 ...

取得最小未使用 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 NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)