oracle和mysql的分组_mysql与oracle的分组函数

mysql的group by与oracle有区别。

在oracle中，分组列以外的列若出现在最终查询结果中，必须要加函数，不能直接输出结果列。

但在mysql中可以直接输出，其结果是按分组列顺序找到的第一条记录，后面的将被忽略。

如：

mysql> create table te(a varchar(2),b varchar(4));

Query OK, 0 rows affected (0.13 sec)

mysql> insert into te values('ab','abc');

Query OK, 1 row affected (0.09 sec)

mysql> insert into te values('ab','bcd');

Query OK, 1 row affected (0.06 sec)

mysql> insert into te values('ab','def');

Query OK, 1 row affected (0.09 sec)

mysql> insert into te values('ac','def');

Query OK, 1 row affected (0.08 sec)

mysql> insert into te values('ac','eef');

Query OK, 1 row affected (0.08 sec)

mysql> select * from te;

+------+------+

| a    | b    |

+------+------+

| ab   | abc  |

| ab   | bcd  |

| ab   | def  |

| ac   | def  |

| ac   | eef  |

+------+------+

5 rows in set (0.00 sec)

mysql> select * from te group by a;

+------+------+

| a    | b    |

+------+------+

| ab   | abc  |

| ac   | def  |

+------+------+

2 rows in set (0.00 sec)

mysql> select * from te group by a;

+------+------+

| a    | b    |

+------+------+

| ab   | abc  |

| ac   | def  |

+------+------+

2 rows in set (0.00 sec)

mysql> select * from te group by a;

+------+------+

| a    | b    |

+------+------+

| ab   | abc  |

| ac   | def  |

+------+------+

2 rows in set (0.00 sec)

mysql> select * from te group by b;

+------+------+

| a    | b    |

+------+------+

| ab   | abc  |

| ab   | bcd  |

| ab   | def  |

| ac   | eef  |

+------+------+

4 rows in set (0.00 sec)

mysql> select * from te group by b;

+------+------+

| a    | b    |

+------+------+

| ab   | abc  |

| ab   | bcd  |

| ab   | def  |

| ac   | eef  |

+------+------+

4 rows in set (0.00 sec)

========================================================

Connected to Oracle Database 10g Release 10.2.0.1.0

Connected as scott

SQL> create table te(a varchar(2),b varchar(4));

Table created

SQL> insert into te values('ab','abc');

1 row inserted

SQL>  insert into te values('ab','bcd');

1 row inserted

SQL> insert into te values('ab','def');

1 row inserted

SQL>

SQL> insert into te values('ac','def');

1 row inserted

SQL> insert into te values('ac','eef');

1 row inserted

SQL> select * from te;

A  B

-- ----

ab abc

ab bcd

ab def

ac def

ac eef

SQL> select * from te group by a;

select * from te group by a

ORA-00979: 不是 GROUP BY 表达式

SQL> rollback;

Rollback complete

========================================================

另外，mysql中一条命令可以包含多个语句，而oracle不行：

mysql> select * from te;select * from te;

+------+------+

| a    | b    |

+------+------+

| ab   | abc  |

| ab   | bcd  |

| ab   | def  |

| ac   | def  |

| ac   | eef  |

+------+------+

5 rows in set (0.00 sec)

+------+------+

| a    | b    |

+------+------+

| ab   | abc  |

| ab   | bcd  |

| ab   | def  |

| ac   | def  |

| ac   | eef  |

+------+------+

5 rows in set (0.00 sec)

SQL> select * from te;select * from te;

select * from te;select * from te

ORA-00911: 无效字符