| 數據庫 | 隨機抽樣 SQL | 關鍵點 |
|---|---|---|
| SQL Server | SELECT TOP (50) * FROM TAB_USERS ORDER BY NEWID(); | NEWID() 生成 GUID,隨機排序 |
| Oracle | SELECT * FROM (SELECT * FROM TAB_USERS ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM <= 50 | 子查詢排序 + ROWNUM 截斷 |
| MySQL | SELECT * FROM TAB_USERS ORDER BY RAND() LIMIT 50 | RAND() + LIMIT |
| PostgreSQL | SELECT * FROM TAB_USERS ORDER BY RANDOM() LIMIT 50 | RANDOM() + LIMIT |
| Access | SELECT TOP 50 * FROM TAB_USERS ORDER BY RND([ID]) | RND(字段) 需傳列名作種子 |
ORDER BY RAND()/NEWID() 需全表掃描+排序,百萬級以上極慢。優化思路:
-- SQL Server (BERNOULLI 行級, SYSTEM 頁級)
SELECT * FROM TAB_USERS TABLESAMPLE SYSTEM (1 PERCENT) REPEATABLE (42)
MIN(ID), MAX(ID),應用層生成隨機 ID 集合,再 WHERE ID IN (...)。OFFSET FLOOR(RAND() * (SELECT COUNT(*) FROM t)) LIMIT 1(MySQL/PostgreSQL),需全表掃描計數。Access SQL 中 RND([字段]) 需傳入字段名作種子,但在 ADO.NET/OleDb 中直接拼接 SQL 易失效。推薦在代碼生成隨機數再拼接:
Random rnd = new Random(Guid.NewGuid().GetHashCode());
int seed = rnd.Next();
string sql = $"SELECT TOP 50 * FROM TAB_USERS ORDER BY RND({seed} * [ID])";
或改用參數化查詢(Access 不支持命名參數,用 ? 位置參數)。
ORDER BY CHECKSUM(NEWID(), 12345) 或 TABLESAMPLE ... REPEATABLE (seed)。DBMS_RANDOM.SEED(12345) 後再查。ORDER BY RAND(12345) / ORDER BY SETSEED(0.12345) THEN RANDOM()。