MySQL subquery usage instance analysis, mysql usage instance analysis

MySQL subquery usage instance analysis, mysql usage instance analysis

This example describes the usage of MySQL subqueries. We will share this with you for your reference. The details are as follows:

Assume that the table my_tbl contains three fields a, B, and c. Now we need to query the minimum value of Column B under 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 'Password'
3 8 'ndf'

Expected result:

A B c
1 3 'cd'
2 3 'nhd'
3 7 'Password'

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

The statement for querying SQL statements that meet the minimum B value is as follows:
Copy codeThe Code is as follows: select A. * from my_tbl as A where A. B = (select min (B) from my_tbl as B where B. a = A. );

Because of the nested query and intersection, the intermediate results were not calculated for an hour in the case of 0.8 million records (I doubt where I wrote the error ); the record quantity will not be discussed later.

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

The specific logic is: first group by column a and column B, and then select the record with the smallest column B value in each group to generate the result set.

The SQL statement is written as follows:
Copy codeThe Code is as follows: select a, B, c, count (a) from (select a, B, c from my_tbl group by a, B) as A group by;

After the query is executed, it takes only 1.1 seconds.

Once again, it is proved that different SQL query policies can directly lead to huge performance differences.