Mysql与Oracle group by的不同之处，mysqloracle

Mysql与Oracle group by的不同之处，mysqloracle

本文原创为freas_1990，转载请标明出处：http://blog.csdn.net/freas_1990/article/details/46310145

在Oracle里，分组与聚合必须是成对出现的，”非分组的字段“必须做聚合操作，否则执行就会报错。而在Mysql里则完全不同。

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未作聚合，却能输出值，当某个first_name的分组记录数大于1的时候，mysql将会输出该分组第一条记录。