經典 SQL 語句大全：24 個實用查詢技巧與範例

前言

SQL（結構化查詢語言）是資料庫操作的核心工具。掌握常用的經典語句，能大幅提升開發效率與查詢效能。本文整理 24 個實用的 SQL 技巧，涵蓋建表、複製、子查詢、連接、分頁、去重等高頻場景，附帶範例碼供參考。

1. 複製表結構（不複製資料）

適用於 Access 與 SQL Server：

-- 方法一：利用永假條件
SELECT * INTO b FROM a WHERE 1 <> 1;

-- 方法二：取前 0 筆
SELECT TOP 0 * INTO b FROM a;

2. 複製表資料

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

3. 跨資料庫複製資料

INSERT INTO b (a, b, c) 
SELECT d, e, f FROM b IN '具體資料庫路徑' WHERE 條件;

4. 子查詢應用

SELECT a, b, c FROM a WHERE a IN (SELECT d FROM b);
-- 或使用列表
SELECT a, b, c FROM a WHERE a IN (1, 2, 3);

5. 顯示文章、提交人與最後回覆時間

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

6. 外部連接查詢

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

7. 線上檢視查詢

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

8. BETWEEN 範圍查詢

BETWEEN 包含邊界值，NOT BETWEEN 則不包含。

SELECT * FROM table1 WHERE time BETWEEN time1 AND time2;
SELECT a, b, c FROM table1 WHERE a NOT BETWEEN 數值1 AND 數值2;

9. IN 條件用法

SELECT * FROM table1 WHERE a [NOT] IN ('值1', '值2', '值4', '值6');

10. 刪除主表中副表不存在的資料

DELETE FROM table1 
WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field1);

11. 四表聯查

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

12. 行程提前 5 分鐘提醒

SELECT * FROM 行程安排 
WHERE DATEDIFF('minute', f開始時間, GETDATE()) > 5;

13. 單一 SQL 實現分頁

SELECT TOP 10 b.* 
FROM (
    SELECT TOP 20 主鍵欄位, 排序欄位 FROM 表名 ORDER BY 排序欄位 DESC
) a, 表名 b 
WHERE b.主鍵欄位 = a.主鍵欄位 
ORDER BY a.排序欄位;

14. 取前 10 筆記錄

SELECT TOP 10 * FROM table1 WHERE 範圍;

15. 分組取最大值記錄

適用於論壇月排行、熱銷商品分析、成績排名等場景。

SELECT a, b, c FROM tablename ta 
WHERE a = (SELECT MAX(a) FROM tablename tb WHERE tb.b = ta.b);

16. EXCEPT 集合差集運算

(SELECT a FROM tableA) 
EXCEPT (SELECT a FROM tableB) 
EXCEPT (SELECT a FROM tableC);

17. 隨機取 10 筆資料

SELECT TOP 10 * FROM tablename ORDER BY NEWID();

18. 隨機選擇記錄

SELECT NEWID();

19. 刪除重複記錄

DELETE FROM tablename 
WHERE id NOT IN (SELECT MAX(id) FROM tablename GROUP BY col1, col2, ...);

20. 列出所有使用者資料表

SELECT name FROM sysobjects WHERE type = 'U';

21. 列出表的所有欄位

SELECT name FROM syscolumns WHERE id = OBJECT_ID('TableName');

22. CASE 條件彙總：交叉表統計

SELECT type,
       SUM(CASE vender WHEN 'A' THEN pcs ELSE 0 END),
       SUM(CASE vender WHEN 'C' THEN pcs ELSE 0 END),
       SUM(CASE vender WHEN 'B' THEN pcs ELSE 0 END)
FROM tablename GROUP BY type;

23. 清空表資料

TRUNCATE TABLE table1;

24. 取第 10~15 筆記錄

SELECT TOP 5 * FROM (
    SELECT TOP 15 * FROM table ORDER BY id ASC
) table_別名 ORDER BY id DESC;