SQL Server 實用語法大全：從基礎 DDL/DML 到進階查詢技巧與分頁優化

SQL 語言分類速查

• DDL：CREATE, ALTER, DROP, DECLARE
• DML：SELECT, DELETE, UPDATE, INSERT
• DCL：GRANT, REVOKE, COMMIT, ROLLBACK

常用基礎語法精選

進階查詢運算子

• UNION / UNION ALL：合併結果集，前者去重。
• EXCEPT / EXCEPT ALL：差集運算。
• INTERSECT / INTERSECT ALL：交集運算。

外部連結完整語法

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

-- RIGHT OUTER JOIN / FULL OUTER JOIN 同理

實戰常用技巧 24 選

1. 分頁查詢：SELECT TOP 10 b.* FROM (SELECT TOP 20 pk, sort FROM t ORDER BY sort DESC) a, t b WHERE b.pk = a.pk ORDER BY a.sort
2. 隨機取 10 筆：SELECT TOP 10 * FROM t ORDER BY NEWID()
3. 刪除重複：DELETE FROM t WHERE id NOT IN (SELECT MAX(id) FROM t GROUP BY col1,col2...)
4. 每組最大值：SELECT * FROM t ta WHERE a = (SELECT MAX(a) FROM t tb WHERE tb.b = ta.b)
5. 動態 SQL 回傳變數：EXEC sp_executesql N'SELECT @c=COUNT(*) FROM t', N'@c INT OUTPUT', @cnt OUTPUT
6. CASE 交叉表：SELECT type, SUM(CASE vender WHEN 'A' THEN pcs ELSE 0 END)... FROM t GROUP BY type

各資料庫隨機查詢語法差異

• Access：SELECT TOP 10 * FROM t ORDER BY RND(id)
• SQL Server：SELECT TOP n * FROM t ORDER BY NEWID()
• MySQL：SELECT * FROM t ORDER BY RAND() LIMIT n

ADO Execute 方法與交易控制

Execute 依查詢與否回傳 Recordset；BeginTrans/CommitTrans/RollbackTrans 實作交易，配合 Errors 集合判斷成敗。