The difference between Mysql and Oracle group by is that mysqloracle

The difference between Mysql and Oracle group by is that mysqloracle

This article is original freas_1990, reprint please indicate the source: http://blog.csdn.net/freas_1990/article/details/46310145

In Oracle, grouping and aggregation must appear in pairs, and "non-grouping fields" must be aggregated. Otherwise, an error is returned. Mysql is completely different.

mysql> select actor.actor_id,actor.first_name from actor join actor_info on (actor.actor_id=actor_info.actor_id) group by actor.first_name;+----------+-------------+| actor_id | first_name  |+----------+-------------+|       71 | ADAM        ||      165 | AL          ||      173 | ALAN        ||      125 | ALBERT      ||       29 | ALEC        ||       65 | ANGELA      ||       76 | ANGELINA    ||       49 | ANNE        ||       34 | AUDREY      ||      196 | BELA        ||       83 | BEN         ||        6 | BETTE       |

select actor.first_name,count(actor.actor_id) from actor join actor_info on (actor.actor_id=actor_info.actor_id) group by actor.first_name;+-------------+----------+| first_name  | count(*) |+-------------+----------+| ADAM        |        2 || AL          |        1 || ALAN        |        1 || ALBERT      |        2 || ALEC        |        1 || ANGELA      |        2 || ANGELINA    |        1 || ANNE        |        1 || AUDREY      |        2 || BELA        |        1 || BEN         |        2 |

Actor_id is not aggregated, but can be output. When the number of group records of a first_name is greater than 1, Mysql will output the first record of the Group.