Mysql subquery Usage Example Analysis _mysql

This example describes the MySQL subquery usage. Share to everyone for your reference, specific as follows:

Suppose the table my_tbl contains three field a,b,c; now you need to query the number of records for which column B is the minimum for each different value of column A in the table.

For example, the table record is:

A b C
1 3 ' CD '
2 3 ' nHD '
1 5 ' BG '
2 6 ' CDs '
1 7 ' Kiy '
3 7 ' VSD '
3 8 ' NDF '

Expect the results to be:

A b C
1 3 ' CD '
2 3 ' nHD '
3 7 ' VSD '

(1) One approach: first find out each a value of the B minimum value, and then based on these minimum values to query all the records that meet the requirements.

Queries that match the minimum B-value SQL are written as follows:

Copy Code code as follows:

Select A.* from My_tbl as A where a.b= (select min (b) from my_tbl as b where b.a=a.a);

Because it is nested query and intersection, 800,000 records in the case unexpectedly in one hours did not calculate the intermediate results (I really doubt where I wrote the wrong);

(2) The above method is a disaster, can only be discarded.

The specific logic is: first by Column A,b group, and then select each group in the column B value of the smallest record, build the result set.

The SQL statement is written as follows:

Copy Code code as follows:

Select A,b,c,count (a) from (select A,b,c to My_tbl Group by A,b) as a group by A;

After executing the query, the time was only 1.1 seconds.

Once again, the differences in SQL's query strategy can directly result in significant performance disparities.

For more information about MySQL interested readers can view the site topics: "MySQL Transaction operation skills Summary", "MySQL stored process skills encyclopedia", "MySQL database lock related skills summary" and "MySQL common function large summary"

I hope this article will help you with the MySQL database meter.