SQL statement for random data reading MsSql/mySql

Source: Internet
Author: User
Tags mssql rand

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.