1. MySQL group by with hidden fields
In standard SQL, if group by is used, the select attribute is either in group by or clustering function. This is also true for the order by attribute. However, for MySQL databases, the Group
So you can use the select list not found in the group
By statement. This indicates "any possible value for this group ". You can achieve better performance by avoiding sorting and grouping unnecessary items. For example, you do not need to group customer. name in the following queries:
Mysql> select
Order. custid, customer. Name, max (payments)
-> From
Order, customer
-> Where
Order. custid = Customer. custid
-> Group
By order. custid;
In MySQL, this name is redundant if it is not run in ANSI mode. Assume that
The column omitted by is not unique in this group. Do not use this function!Non-predictive results (the sorting results are unstable).
Group by: http://dev.mysql.com/doc/refman/5.1/zh/functions.html#group-by-functions-and-modifiers with implicit Fields
2. execution sequence of each stage in the SELECT statement
(8) Select (9) distinct (11) <top num> <select list>
(1) From [left_table]
(3) <join_type> join <right_table>
(2) On <join_condition>
(4) Where <where_condition>
(5) group by <group_by_list>
(6) with <cube | rollup>
(7) having (10) order by <order_by_list>
Execution sequence of each stage in a select statement: http://www.cnblogs.com/laodao1/archive/2010/01/07/1641259.html
Http://bbs.csdn.net/topics/390160683