[Mysql] example of subquery _ MySQL

[Mysql] An example of subquery bitsCN.com

[Mysql] An example of subquery

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 method is to 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:

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:

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.

BitsCN.com