SQL 進階技巧集錦：從菜鳥到高手的經典語句解析

經典 SQL 模式速查

1. 復制表結構 (無數據)

-- SQL Server / Access
SELECT * INTO new_table FROM old_table WHERE 1=0;

-- MySQL / PostgreSQL
CREATE TABLE new_table LIKE old_table;
-- 或
CREATE TABLE new_table AS SELECT * FROM old_table LIMIT 0;

2. 複製數據 (結構已存在)

INSERT INTO target (col1, col2, col3)
SELECT src_col1, src_col2, src_col3 FROM source;

3. 顯示文章、提交人、最後回復時間

SELECT a.title, a.username, b.last_reply
FROM articles a
JOIN (
    SELECT title, MAX(addtime) AS last_reply
    FROM replies GROUP BY title
) b ON a.title = b.title;

4. 外連接查詢 (LEFT JOIN)

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

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

-- SQL Server
SELECT * FROM schedule 
WHERE DATEDIFF(minute, start_time, GETDATE()) > 5;

-- MySQL
SELECT * FROM schedule 
WHERE start_time <= DATE_ADD(NOW(), INTERVAL 5 MINUTE);

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. 隨機抽樣

-- MySQL
SELECT * FROM tbl ORDER BY RAND() LIMIT 10;

-- SQL Server
SELECT TOP 10 * FROM tbl ORDER BY NEWID();

-- PostgreSQL
SELECT * FROM tbl TABLESAMPLE BERNOULLI (10);  -- 近似 10%

9. 分組取 Top N (窗口函數)

-- SQL Server / PostgreSQL / MySQL 8.0+
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
) t WHERE rn <= 3;

10. 遞歸查詢 (CTE)

WITH RECURSIVE org_tree AS (
    SELECT id, name, parent_id, 1 AS level
    FROM org WHERE parent_id = 0
    UNION ALL
    SELECT o.id, o.name, o.parent_id, ot.level + 1
    FROM org o JOIN org_tree ot ON o.parent_id = ot.id
)
SELECT * FROM org_tree;

高手養成建議

1. 精通 执行计划 (EXPLAIN / SET STATISTICS IO ON)
2. 掌握 索引設計：覆蓋索引、前綴索引、下推條件
3. 熟練 窗口函數 (ROW_NUMBER, LAG, LEAD, NTILE)
4. 善用 CTE 拆解複雜邏輯，提升可讀性
5. 理解 事務隔離級別 與鎖機制，避免死鎖
6. 建立 SQL 審核規範：命名、縮進、註釋、參數化