隨機讀取資料sql語句MsSql/mySql

SQL Server隨機讀取一個表中若干條記錄：

樣本：

代碼如下	複製代碼
select top 10 id,title from news order by newid()

最佳化之後

在MSSQL中用以下方法隨機讀取3W多條資料，只用了1-2秒。而在實際運用中，肯定是不會隨機讀取這麼多資料的

MSSQL

代碼如下	複製代碼
select id from tablename where id >= (select floor(rand() * ((select max(id) from tablename) - (select min(id) from tablename)) + (select min(id) from tablename))) order by id

mySql 隨機讀取資料

rand()函數

代碼如下	複製代碼
select * from users order by rand() LIMIT 1

這條語隨機取一條記錄還是不錯的選擇

代碼如下	複製代碼
SELECT * FROM users AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(userId) FROM `users`)-(SELECT MIN (userId) FROM users))+(SELECT MIN(userId) FROM users)) AS userId) AS t2 WHERE t1.userId >= t2.userId ORDER BY t1.userId LIMIT 1

執行該sql語句，用時0.039s，效率太給力了

代碼如下	複製代碼
SELECT * FROM users WHERE userId >= ((SELECT MAX(userId) FROM users)-(SELECT MIN(userId) FROM users)) * RAND() + (SELECT MIN(userId) FROM users) LIMIT 1