MySQL Overview (iv) Order By,group features, subquery

1.order by default in ascending order (ASC/DESC), multi-field sort order By field, Field 2 sort by: In a grouped sort, sorting is the sort of ending that is grouped, not in the group.

2.limit in the statement is best, intercept the number of records. Use with general and order by

Limit[offset][n]

Offset: N: The number of bars, such as limit (2,2), which takes two records starting from the third record. When a parameter: Limit 2, that is, starting from the first record to fetch 2

3 A question about grouping and aggregation functions, when grouping the corresponding data as group data from the library when the group first appears, and when using aggregate functions, the column values corresponding to the aggregate function parameters are not corresponding to the group. Just a combination such as: Group by class, get the best students, select name, Max (score) from Stu Group by Bianji; Such a maximum achievement corresponds to the pupil and the name corresponding to the pupil is not corresponding.

3.1. If you want to get the best student information for your class in the school student table, if you want to use a simple grouping is not possible, the above has been explained, and you can not guarantee that there will be more than the same results.

Method One: First use the group to query out the class and the results of the maximum results as a new table, and the original table to jointly query out the corresponding student information.

Method Two: If the best results have only one word of the special column, it is possible to sort the result set by class and score before grouping (using the group to get the first occurrence of the rule). Items similar to the highest price for each item in a product:

4 sub-query where from exists

WHERE subquery: aggregate functions can be used directly in the group, but cannot be used with other fields

Use different sub-query to find out the latest products under the column (only the one with the largest ID is the latest)

where

From

Exists: Query out a product column: You can understand each record in the query, substituting into the query, if the query has the results, it indicates that the current record of the outer query to meet the conditions.

MySQL Overview (iv) Order By,group features, subquery