SQL statement for random data reading MsSql/mySql

SQL Server randomly reads several records from a table:

Example:

The code is as follows:	Copy code
Select top 10 id, title from news order by newid ()

After optimization

In MSSQL, the following method is used to randomly read more than data records, which takes 1-2 seconds. In practical use, it is certainly not to read so much data randomly.

MSSQL

The code is as follows:	Copy code
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 reads data randomly.

Rand () functions

The code is as follows:	Copy code
Select * from users order by rand () LIMIT 1

It is a good choice to randomly retrieve a record for this sentence.

The code is as follows:	Copy code
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

It takes 0.039 s to execute this SQL statement, which is too efficient.

The code is as follows:	Copy code
SELECT * FROM users WHERE userId> = (select max (userId) FROM users)-(select min (userId) FROM users )) * RAND () + (select min (userId) FROM users) LIMIT 1