The day before yesterday because the work required me to take a random few records from a database of 5W records, here I am directly using the MySQL rand by function to direct, thousands of records okay, but if the tens of thousands of feeling to a
Random query, the method can have many kinds. For example, all records are queried, and N records are randomly taken from the list. The use of the program can be implemented. However, the program implementation must query all qualifying records (at
Reprint Http://hi.baidu.com/my_favourate/item/3716b0cbe125f312505058ebSQL query multiple non-repeating record values brief analysis 2008-02-28 11:36 below test under Oracle 10gBecause of the design needs, need to query the record value of the same
There are a few commonly used tables under the Scott table, and there are data. are EMP, dept, Salgrade, respectively;1. View table structure with DESC DESC EMP;2, empty table dual, the most commonly used empty table, such as: Select 2 * 4
I just made a data extraction of 50 million data records. The current table is still performing the insert operation, with hundreds of data entries per minute. Therefore, the query efficiency after the extra-score area is improved by more than 10
SQL: delete duplicate data. Only one record is retained.
SQL2010-09-10 16:03:33 Read82 Comment0 Font Size:LargeMediumSmall Subscription There are some identical records in thousands of records. How can I use SQL statements to delete duplicates?
Oracle subquery, oracle subquery
Subquery: contains another query within a query.
Common subquery
-- Query the information of all employees whose salaries are higher than 7654. select * from emp e where e. sal> (select sal from emp where empno = 7
If not object_id (' Tempdb. #T ') is nulldrop table #TGoCreate table #T ([ID] int,[name] nvarchar (1), [Memo] nvarchar (2))Insert #TSelect 1,n ' A ', N ' A1 ' UNION ALLSelect 2,n ' A ', N ' A2 ' UNION ALLSelect 3,n ' A ', N ' A3 ' UNION ALLSelect
Mysql random query data bitsCN.com
To query five non-duplicate data records in mysql, use the following:
1 SELECT * FROM `table` ORDER BY RAND() LIMIT 5
You can. However, the test results show that the efficiency is very low. It takes more than 8
Sub-querySub-query refers to a complete query, nested in a number of different functions of small queries, so as to complete a complex query a form of writing, in order to let the reader more clearly understand the concept of sub-query.Sub-query
MySQL often need to obtain random data, for example, to take a random record from the TableName table, the general way of writing is:
The code is as follows
Copy Code
SELECT * FROM TableName ORDER by RAND () LIMIT 1
Create a table that holds the ID information for the record you want to delete:
The code is as follows
Copy Code
CREATE TABLE ' Tmp_ids ' (' ID ' int (11),' Name ' char (20)) Engine=myisam;
If you want to
Verification: MySQL Version 5.6Method One:Delete A from table a LEFT join (Select (ID) from table Group by Studentname,classid) b to a.id=b.id where b.id is null;Method Two:Explain delete from table where ID not in (select MiniD from (select min (id)
Subquery: Contains another query inside a query.Normal subquery--Query for information about all employees that are higher than the 7654 wage select * from emp e where e.sal > (select Sal from emp where empno = 7654);--query out wages higher than 765
Today a classmate asked me MySQL to remove duplicate data, I made a test by the way recorded:To view the table structure:Mysql> desc testdelete;+-------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key |
--Seventh Chapter--1, check the earliest and latest date of entry dateSelect min (hiredate), Max (HireDate)From EMP--2. Check the average salary of all employees starting with sales, minimum wage, most-high wages, wages andSelect AVG (sal), Min (Sal)
Get a random piece of data from a data table with an SQL statement--Rand () gets a random floating point number between 0 and 1--((SELECT Max (ID) from test)-(select min (id) from test)) * RAND () gets to random number--+ (select min (id) FR OM test)
Subquery: Includes another query within one query.Normal subquery--Find out information about all employees who are higher than 7654 salary SELECT * from emp e where e.sal > (select Sal from emp where empno = 7654);--query out the wage ratio of 7654
Although the SQL statements for querying and deleting duplicate records are messy, the content is still good.
Although the SQL statements for querying and deleting duplicate records are messy, the content is still good.
For example:
Id name value
1
I always thought that mysql would randomly query several pieces of data and use SELECT * FROM 'table' ORDERBYRAND () LIMIT5. However, the test results show that the efficiency is very low. It takes more than 8 seconds for a database with more than 0.
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.