SQL 進階語法寶典：CASE 轉換、排序規則、分頁隨機、範圍查詢與備份還原實用腳本

實戰沉澱：高頻進階 SQL 技巧總彙整

工作中反覆用到卻容易遺忘的進階語法，整理成速查手冊。涵蓋資料轉換、中文排序、模糊範圍、隨機抽樣、集合運算、延遲執行、條件插入等實戰場景。

1. CASE 條件轉換：資料呈現層最佳拍檔

-- 簡單 CASE (等值判斷)
CASE Type WHEN '1' THEN '普通通道' WHEN '2' THEN '高端通道' ELSE '其他' END AS TypeName

-- 搜尋 CASE (複雜條件)
CASE 
    WHEN Score >= 90 THEN '優'
    WHEN Score >= 60 THEN '及格'
    ELSE '不及格'
END AS Grade

應用：報表顯示、動態 SQL 產生、ETL 清洗轉換。

2. 系統表查詢：取得使用者資料表清單

-- SQL Server
SELECT name FROM sysobjects WHERE xtype = 'U' ORDER BY name;
-- 或 (較新版本)
SELECT name FROM sys.tables WHERE is_ms_shipped = 0;

3. 中文排序：拼音與筆畫雙模式

-- 拼音排序 (區分大小寫、假名、全半形)
SELECT * FROM Customers ORDER BY Name COLLATE Chinese_PRC_CS_AS_KS_WS;

-- 筆畫排序
SELECT * FROM Customers ORDER BY Name COLLATE Chinese_PRC_Stroke_CI_AS;

COLLATE 子句 可用於欄位定義、資料庫預設、查詢指定。跨庫 JOIN 時若 Collation 不同會報錯「無法解決排序規則衝突」，解法：COLLATE DATABASE_DEFAULT 或統一實例級 Collation。

4. LIKE 範圍與排除模式

適用：資料清洗、格式驗證 (如電話、郵編)。

5. 隨機排序抽樣

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

-- MySQL / PostgreSQL
SELECT * FROM BigTable ORDER BY RAND() LIMIT 10;  -- 大表慎用，全表掃描

大表 `ORDER BY RAND()/NEWID()` 效能極差。生產環境改用：TABLESAMPLE (SQL Server)、WHERE RAND() < 0.01 伯努利採樣、或應用層抽樣 ID。

6. 集合運算：交集/聯集/差集

-- 交集 (兩表皆有)
SELECT a FROM TableA INTERSECT SELECT b FROM TableB;
-- 或 INNER JOIN
SELECT A.a FROM TableA A INNER JOIN TableB B ON A.a = B.b;

-- 聯集 (去重)
SELECT a FROM TableA UNION SELECT b FROM TableB;
-- 聯集 (不去重，快)
SELECT a FROM TableA UNION ALL SELECT b FROM TableB;

-- 差集 (在 A 不在 B)
SELECT a FROM TableA EXCEPT SELECT b FROM TableB;
-- 或 LEFT JOIN + IS NULL
SELECT A.a FROM TableA A LEFT JOIN TableB B ON A.a = B.b WHERE B.b IS NULL;

7. 延遲執行：排程/測試利器

-- 等待 5 秒
WAITFOR DELAY '00:00:05';
SELECT * FROM LogTable;

-- 等待到指定時間 (23:08)
WAITFOR TIME '23:08:00';
EXEC usp_NightlyJob;

8. 條件插入：避免重複鍵衝突 (UPSERT 前身)

INSERT INTO TargetTable (TraceKey, MuteSMS, CreateTime, TraceUser, TraceTime, TraceSlot, TraceDuration)
SELECT 'TRACE_TIMER', 0, GETDATE(), MobileID, GETDATE(), '30', '0'
FROM SourceTable
WHERE CorpID = 10001
  AND NOT EXISTS (SELECT 1 FROM TargetTable WHERE TraceUser = SourceTable.MobileID)
  AND MobileID LIKE '13%' AND LEN(MobileID) = 11;

關鍵：`NOT EXISTS` 子查詢關聯外層欄位，實現「不存在才插入」。

9. 動態 SQL 建構與執行

DECLARE @SQL NVARCHAR(MAX) = N'';
SELECT @SQL += 'UPDATE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) 
               + ' SET UpdatedAt = GETDATE();' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
EXEC sp_executesql @SQL;

必須配合 `QUOTENAME` 防注入，`sp_executesql` 參數化執行。

10. 分頁查詢標準寫法 (SQL Server 2012+)

SELECT * FROM Orders
ORDER BY OrderDate DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;  -- 第 3 頁，每頁 10 筆

MySQL/PostgreSQL: `LIMIT 10 OFFSET 20`。Oracle 12c+: `OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY`。