在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将会输出该分组第一条记录
。