Mysql與Oraclegroupby的不同之處

Mysql與Oraclegroupby的不同之處

在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 |