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將會輸出該分組第一條記錄。