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: 无效字符

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-766951/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-766951/