ITPub博客

首页 > Linux操作系统 > Linux操作系统 > mysql与oracle的分组函数

mysql与oracle的分组函数

原创 Linux操作系统 作者:to_be_dba 时间:2013-07-23 23:06:51 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-11-23

  • 博文量
    148
  • 访问量
    396313