mysql與oracle的分組函式

to_be_Dba發表於2013-07-23
MySqlOracle函式
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章