[Mysql] An example of subquery

[Mysql] An example of subquery assumes 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 c1 3 'cd' 2 3 'nhd' 1 5 'bg '2 6 'cd' 1 7'kiy' 3 7'bf'3 8'ndf' expected results is: a B c1 3 'cd' 2 3 'nhd '3 7' _ (1) One of the practices is to first find the minimum value of B under each a value, query all records that meet the requirements based on the minimum values. The statement for querying SQL statements that meet the minimum B value is as follows: select. * from my_tbl as A where. 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 really suspect that I wrote something wrong ); 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.