oracle和mysql的分组_Mysql与Oracle group by的不同之处

本文原创为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将会输出该分组第一条记录。