經典 SQL 進階技巧大全：子查詢、連接、分頁、去重、跨庫操作實戰

經典 SQL 進階技巧大全

掌握這些進階 SQL 模式，能解決 90% 的業務查詢與數據處理場景。本文涵蓋表複製、跨庫操作、子查詢、外連接、視圖、範圍查詢、IN 條件、關聯刪除、多表連接、排程提醒等實戰技巧，兼容 SQL Server/MySQL/PostgreSQL (部分語法需微調)。

1. 表結構與數據複製

-- 僅複製結構 (無數據)
-- SQL Server / PostgreSQL:
SELECT * INTO new_table FROM source_table WHERE 1=0;
-- MySQL:
CREATE TABLE new_table LIKE source_table;

-- 複製結構+數據
INSERT INTO target_table (col1, col2) SELECT col1, col2 FROM source_table;

-- 跨庫複製 (SQL Server 鏈接服務器 / MySQL FEDERATED / PostgreSQL dblink)
INSERT INTO db2.dbo.target (col1, col2) SELECT col1, col2 FROM db1.dbo.source WHERE condition;

2. 子查詢與 IN / EXISTS

-- IN 子查詢
SELECT a, b, c FROM table_a WHERE a IN (SELECT d FROM table_b);

-- IN 值列表
SELECT * FROM table1 WHERE status IN ('active', 'pending', 'approved');

-- NOT EXISTS 關聯刪除 (刪除主表中副表無對應記錄的行)
DELETE FROM table1
WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table1.field1 = table2.field1);

-- 關聯更新
UPDATE table1 t1
SET t1.col = (SELECT col2 FROM table2 t2 WHERE t1.id = t2.id)
WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t1.id = t2.id);

3. 連接查詢 (JOIN)

-- 左外連接 (保留左表所有行)
SELECT a.col1, a.col2, b.col3, b.col4
FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id;

-- 多表連接 (四表)
SELECT * FROM a
LEFT JOIN b ON a.id = b.a_id
RIGHT JOIN c ON a.id = c.a_id
INNER JOIN d ON a.id = d.a_id
WHERE ...;

4. 內聯視圖 / 衍生表

SELECT * FROM (
    SELECT a, b, c FROM table_a
) t WHERE t.a > 1;

5. BETWEEN 範圍查詢

-- 包含邊界值
SELECT * FROM table1 WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31 23:59:59';

-- NOT BETWEEN 排除範圍
SELECT * FROM table1 WHERE age NOT BETWEEN 18 AND 60;

6. 文章/帖子列表：顯示最後回覆時間

SELECT a.title, a.author, b.last_reply_time
FROM posts a
JOIN (
    SELECT post_id, MAX(reply_time) AS last_reply_time
    FROM replies
    GROUP BY post_id
) b ON a.id = b.post_id;

7. 排程提醒：提前 N 分鐘

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

-- MySQL
SELECT * FROM schedule WHERE TIMESTAMPDIFF(MINUTE, start_time, NOW()) > 5;

-- PostgreSQL
SELECT * FROM schedule WHERE EXTRACT(EPOCH FROM (NOW() - start_time))/60 > 5;

8. 分頁查詢 (標準 SQL:2008)

-- MySQL / PostgreSQL / SQLite
SELECT * FROM table_name ORDER BY id LIMIT 20 OFFSET 40;

-- SQL Server 2012+ / Oracle 12c+ / PostgreSQL
SELECT * FROM table_name ORDER BY id OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

9. 去重查詢

-- 保留第一條 (按 id)
SELECT DISTINCT ON (email) * FROM users ORDER BY email, id;  -- PostgreSQL

-- MySQL 8.0+ 窗口函數
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
) t WHERE rn = 1;

10. 避免 SELECT * 的習慣

明確列出所需欄位：減少網絡傳輸、利用覆蓋索引、降低鎖競爭、便於維護。

11. 事務控制模板

BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  IF @@ERROR <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION;

提示： 不同數據庫的語法細節 (如 LIMIT/OFFSET、DATEDIFF、自動增長、字符串拼接) 有差異，實際使用請查閱對應版本文檔。