Detailed description of exists in MySQL

Before encountering a problem, the following brief description:

The table structure is as follows:

CREATE TABLE ' Testa ' (
' id ' int (one) not NULL auto_increment,
' CID ' int (one) DEFAULT NULL comment ' Product ID ',
' UID ' int (one) DEFAULT NULL comment ' User ID ',
' Buytime ' int (one) DEFAULT NULL comment ' buy time ',
PRIMARY KEY (' id ')
) Engine=innodb auto_increment=0 DEFAULT charset=utf-8;

Use the following SQL to randomly insert a batch of test data:

INSERT into Testa (cid,uid,buytime) VALUES (rand () *5,rand () *5,rand () *10000);

Description: Rand () returns a random decimal number from 0 to 1, for example: 0.4578285091163856, which automatically rounds out the storage when the database is stored, since several fields in the previous table are of type int.

OK, here's the point: Find out the first three customer IDs for each product purchased in the table. Describe a good simple problem, at that time stumped bloggers, after a multi-query data, finally got the correct answer:

SELECT * from Testa as a where not EXISTS (SELECT * from Testa as b where a.cid = B.cid and A.buytime < B.buytime group by B.cid have count (*) > 2);

Here are a few key methods, exists, group by and having;

According to a single understanding: exists to determine whether the expression inside the parentheses returns the result is true, return true will be the current main table data out, otherwise discard the result (Bo Master wrote the article only know so much, not in depth) not exists and exists function mutually exclusive, do not do too much explanation.

Group by does not have to explain too much, the current query results according to a field to group, go heavy.

Having the function is the method of processing the final result of MySQL execution two times.

First of all, just get this SQL, think exists should be similar to in function, first execute the method inside the parentheses, get all the results returned to the main table for two queries, but group by after the data conditions simply can not meet, and ultimately turn to the Almighty knight, Finally understand the SQL processing process, the following description:

Because group by is executed after the where condition, the where association query for a and B tables in the subquery is executed first, and true is returned after the where condition is executed, so exists executes as true.

Here is a description of the exists execution process: first in a table with the first data and B table of all the data, to get all the data is greater than the current a table of the purchase time of the B-table data, according to the product of table B, and COUNT (*) will remember the current results obtained by the number of rows, Although having this time has not yet entered into force; then executes the second data of table A, then loops through all the data in table A, and then organizes the results by having all the queries with rows greater than 2 are discarded (not EXISTS), resulting in the final result.

Note: Because we want to take the earliest three customers, the smaller the purchase time ranked higher, and when the first early customer is executed, A.buytime < b.buytime the condition count (*) Returns the result is 0.

after the reorganization, SQL can write this: SELECT * from Testa as B where EXISTS (SELECT * from Testa as C where c.cid = B.cid and b.buytime <= c.buytime Grou P by C.cid have count (*) <= 3);

Loan Article Address:

http://blog.csdn.net/qsyzb/article/details/12523051

http://fucheng.blog.51cto.com/2404495/1575693

Detailed description of exists in MySQL