Special cases of MySQL group by, select, and order

Source: Internet
Author: User

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


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.