資料庫開發 80% 時間在寫同類查詢。本文整理 23 條高頻 SQL 模板，覆蓋數值/字串/日期/邏輯查詢、分頁、去重、分組統計、多表關聯，每條附帶標準語法與實戰場景，建議收藏為代碼片段庫。

1-4 基礎條件查詢

-- 1. 數值範圍
SELECT * FROM orders WHERE amount > 1000;

-- 2. 字串精確/模糊
SELECT * FROM users WHERE username = 'john';
SELECT * FROM users WHERE username LIKE 'john%';      -- 前綴
SELECT * FROM users WHERE username LIKE '%admin%';    -- 包含
SELECT * FROM users WHERE username LIKE '%_admin';    -- 後綴(_匹配單字符)

-- 3. 日期查詢（跨資料庫兼容寫法）
-- MySQL/SQL Server
SELECT * FROM events WHERE event_date = '2024-01-15';
-- Access
SELECT * FROM events WHERE event_date = #2024-01-15#;

-- 4. 邏輯布林
SELECT * FROM flags WHERE is_active = TRUE;  -- 或 1/'T'
SELECT * FROM flags WHERE is_active = FALSE; -- 或 0/'F'

5-7 非空、變量綁定、動態 LIKE

-- 5. 非空（注意 NULL != ''）
SELECT * FROM profiles WHERE bio <> '' AND bio IS NOT NULL;

-- 6. 參數化數值查詢（防注入）
PREPARE stmt FROM 'SELECT * FROM products WHERE id = ?';
SET @pid = 123; EXECUTE stmt USING @pid;

-- 7. 參數化模糊查詢
PREPARE stmt FROM "SELECT * FROM articles WHERE title LIKE CONCAT('%',?,'%')";
SET @kw = 'mysql'; EXECUTE stmt USING @kw;

8-10 分頁三部曲

-- 8. 前 N 條
SELECT * FROM logs ORDER BY created_at DESC LIMIT 10;

-- 9. 後 N 條（需唯一排序鍵）
SELECT * FROM logs ORDER BY created_at ASC LIMIT 10;

-- 10. 偏移分頁（深分頁性能差，見優化篇）
SELECT * FROM logs ORDER BY id ASC LIMIT 1000, 20;

11-13 統計與時間窗口

-- 11. 統計前 N（如總分排名）
SELECT *, (chinese+math+english) AS total
FROM scores ORDER BY total DESC LIMIT 10;

-- 12. 指定時間窗口（含邊界最佳寫法）
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

-- 13. 按月/年分組統計
SELECT YEAR(created_at) AS yr, MONTH(created_at) AS mon, COUNT(*) AS cnt
FROM orders GROUP BY yr, mon ORDER BY yr DESC, mon DESC;

14-16 範圍、去重、NOT 邏輯

-- 14. 大於條件排序
SELECT * FROM users WHERE age > 18 ORDER BY age;

-- 15. 去重（DISTINCT 必配合 WHERE，且不可用 *）
SELECT DISTINCT department FROM employees WHERE status='active';

-- 16. NOT 組合
SELECT * FROM products WHERE price NOT BETWEEN 100 AND 200;
SELECT * FROM users WHERE status IS NOT NULL;
SELECT * FROM logs WHERE level NOT IN ('DEBUG','INFO');

17-19 重複記錄檢測與排序

-- 17. 找出重複記錄及計數
SELECT name, age, COUNT(*) AS dup_cnt
FROM users GROUP BY name, age HAVING COUNT(*) > 1;

-- 18. 多欄位混合排序
SELECT * FROM products ORDER BY category ASC, price DESC, created_at ASC;

-- 19. 彙總結果排序
SELECT category, SUM(price) AS total_revenue
FROM orders GROUP BY category ORDER BY total_revenue DESC;

20-23 分組統計進階

-- 20. 單列分組彙總
SELECT product_id, SUM(price) AS revenue, COUNT(*) AS sales
FROM order_items GROUP BY product_id ORDER BY revenue DESC;

-- 21. 多列分組（非聚合列必須出現在 GROUP BY）
SELECT region, channel, SUM(amount) AS gmv
FROM sales GROUP BY region, channel ORDER BY gmv DESC;

-- 22. 跨表分組（別名簡化）
SELECT a.name, AVG(a.price), b.name, AVG(b.price)
FROM demo a JOIN demo_1 b ON a.id = b.id GROUP BY b.type;

-- 23. 條件聚合（單掃描多指標）
SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END) AS paid_cnt,
  SUM(CASE WHEN status='refund' THEN amount ELSE 0 END) AS refund_amt
FROM orders;

避坑提醒

• DISTINCT 作用於所有選中列，DISTINCT a,b ≠ DISTINCT a。
• GROUP BY 必須包含所有非聚合選中列（ONLY_FULL_GROUP_BY 模式強制）。
• 分頁深度 > 10 萬時，必須用 游標分頁 / 覆蓋索引 / 延遲關聯 優化。
• 日期範圍查詢嚴禁 對列用函數，統一用半開區間 [start, end)。

這 23 條模板覆蓋日常開發 90% 以上查詢場景。將其封裝為 IDE Live Templates / Snippets，配合參數化查詢習慣，能讓你從「寫 SQL」進化為「組裝 SQL」，專注業務邏輯而非語法細節。