創意 SQL 實用技巧集 (二)：欄位重新編號、排名、跨庫查詢、Excel 讀取、指定排序規則

技巧 1：單一陳述式重新編號欄位 (遞增序號)

DECLARE @i INT = 0;
UPDATE Table1 SET @i = @i + 1, Field1 = @i;

利用變數在 UPDATE 中累加，無需游標/迴圈，效能極佳。適用於補齊中斷的身分欄位、產生顯示序號。

技巧 2：純 SQL 計算排名 (成績排名)

UPDATE GradeTable a
SET a.Rank = (
    SELECT COUNT(*) + 1
    FROM GradeTable b
    WHERE a.TotalScore < b.TotalScore
);

相關子查詢計算「比我高分的人數 + 1」。同分同排名需改用 DENSE_RANK() (SQL 2005+)。

技巧 3：OPENROWSET 查詢外部 Access 資料庫

SELECT a.* 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Test.mdb'; 'admin'; '', Table1) a;

無需建立連結伺服器，臨時跨庫查詢。注意 64 位元環境需 ACE Provider。

技巧 4：OPENDATASOURCE 直接查詢 Excel 檔案

SELECT * 
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 
    'Data Source="C:\Test.xls";User ID=Admin;Password=;Extended Properties=Excel 8.0')...[Sheet1$];

工作表名加 $ 並用方括號。HDR=YES 視第一列為標題。

技巧 5：查詢中指定 COLLATE 排序規則 (解決大小寫敏感)

-- 欄位級指定
SELECT * FROM Table1 ORDER BY Field1 COLLATE Chinese_PRC_BIN;

-- 條件級指定 (登入驗證必須大小寫敏感)
SELECT COUNT(*) FROM Pub_Users 
WHERE UserName = 'admin' AND PassWord = 'aaa' COLLATE Chinese_PRC_BIN;

關鍵：預設排序規則通常 不分大小寫 (CI)。密碼比對**必須**加 COLLATE ..._BIN (二進位比較) 或 ..._CS_AS (區分大小寫/重音)，否則 'aaa'='AAA' 會通過驗證，造成安全漏洞。

技巧 6：ORDER BY 使用欄位序數 (避免重複複雜運算式)

SELECT a.ID, a.Name, (SELECT COUNT(*) FROM TableB b WHERE a.ID = b.PID) AS Cnt
FROM TableA a
ORDER BY 3;  -- 直接按第 3 個 SELECT 列排序

第 3 列為子查詢且無別名時，ORDER BY 3 極其方便。注意：SQL Server 支援，Oracle/PostgreSQL 亦支援，但 ANSI 標準不建議，視團隊規範決定。

補充資源連結

• Microsoft SQL Server 2005 產品指南 (詳細功能參考)
• SQL Server 農曆計算函數 (自訂函數實作)

結語

這六個技巧涵蓋資料整理、排名、異構資料存取、安全比對、排序簡化，均為實戰高頻場景。掌握並內化為肌肉記憶，能大幅提升 SQL 開發效率與品質。