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