MySQL GROUP by usage misunderstanding

1. Description

"GROUP BY" is the literal understanding that the data is grouped according to the rules specified by the "by", in which the so-called grouping is dividing a "dataset" into several "small regions" and then processing data for several small regions. (Just a brief explanation of the statement's role, not the focus of this article)

2. Examples of use:

2.1 Table structure Type

Mysql> desc actor;+-------------+----------------------+------+-----+-------------------+-------------------- ---------+| Field       | Type                 | Null | Key | Default           | Extra                       |+-------------+----------------------+------+-----+-------------------+----------------------------- +| actor_id    | smallint (5) unsigned | NO   | PRI | NULL              | auto_increment              | | first_name  | varchar |          NO   |     | NULL              |                             | | last_name   | varchar |          NO   | MUL | NULL              |                             | | last_update | timestamp            | NO   |     | Current_timestamp | On update current_timestamp |+-------------+----------------------+------+-----+-------------------+------------- ----------------+4 rows in Set (0.00 sec)

2.2 Use:

　　

Mysql> Select First_name,last_name from  actor Group by first_name,last_name;+-------------+--------------+| First_Name  | last_name    |+-------------+--------------+| ADAM        | GRANT        | | ADAM        | HOPPER       | | AL          | GARLAND      | | ALAN        | Dreyfuss     | | ALBERT      | JOHANSSON    | | ALBERT      | NOLTE        | | ALEC        | WAYNE        | | ANGELA      | HUDSON       | | ANGELA      | Witherspoon  | | Angelina    | Astaire      | | ANNE        | Cronyn       | | AUDREY      | BAILEY       | | AUDREY      | OLIVIER      | | BELA        | Walken       | | BEN         | HARRIS       | | BEN         | WILLIS       | | BETTE       | NICHOLSON    | | BOB         | FAWCETT      | | BURT        | Dukakis      |

3. Other

We know that when we contacted group by earlier, we were told that the field specified in select is either to be included behind the group by statement, or to be included in the aggregate function. As used above, can I show the field in the back of the select instead of the Group by field? See if the following SQL will execute properly?

Mysql> Select First_name,last_name from  actor Group by actor_id;

The results may be unexpected and look at the results of the implementation:

Mysql> Select First_name,last_name from  actor Group by actor_id;+-------------+--------------+| first_name  | last_name    |+-------------+--------------+| PENELOPE    | Guiness      | | NICK        | Wahlberg     | | ED          | CHASE        | | JENNIFER    | DAVIS        | | JOHNNY      | Lollobrigida | | BETTE       | NICHOLSON    | | GRACE       | Mostel       | | MATTHEW     | JOHANSSON    | | JOE         | Swank        | | CHRISTIAN   | GABLE        | | ZERO        | CAGE         | | KARL        | BERRY        | | UMA         | WOOD         | | VIVIEN      | BERGEN       | | CUBA        | OLIVIER      | | FRED        | COSTNER      | | HELEN       | Voight       | | DAN         | Torn         | | BOB         | FAWCETT      | | Lucille     | TRACY        | | KIRSTEN     | Paltrow      | | ELVIS       | MARX         |

4. Analysis

What's the situation? Not that if the field specified by select is either in group by or inside an aggregate function? The SQL above me does not specify the corresponding fields in the group by is also able to execute properly!!! I checked the official MySQL document before I knew: if the fields specified by select are not in group by, then it is important to ensure that they are directly dependent on the fields that follow group by. For example, first_name,last_name all rely on the primary key actor_id, so the above result is OK.

5. Expansion

It should be noted that in MySQL, when indexes are unavailable, group by uses two strategies: grouping using temporary tables or sorting files. (Other ...) ）

MySQL GROUP by usage misunderstanding