Oracle和MySQL分组查询GROUP BY

真题1、Oracle和MySQL中的分组（GROUP BY）有什么区别？
答案：Oracle对于GROUP BY是严格的，所有要SELECT出来的字段必须在GROUP BY后边出现，否则会报错：“ORA-00979: not a GROUP BY expression”。而MySQL则不同，如果SELECT出来的字段在GROUP BY后面没有出现，那么会随机取出一个值，而这样查询出来的数据不准确，语义也不明确。所以，作者建议在写SQL语句的时候，应该给数据库一个非常明确的指令，而不是让数据库去猜测，这也是写SQL语句的一个非常良好的习惯。
下面给出一个示例。有一张T_MAX_LHR表，数据如下图所示，有3个字段ARTICLE、AUTHOR和PRICE。请选出每个AUTHOR的PRICE最高的记录（要包含所有字段）。

ARTICLE	AUTHOR	PRICE
0001	B	3.99
0002	A	10.99
0003	C	1.69
0004	B	19.95
0005 http://www.cndba.cn/lhrbest/article/2029	A	6.96

首先给出建表语句：
CREATE TABLE T_MAX_LHR (ARTICLE VARCHAR2(30),AUTHOR VARCHAR2(30),PRICE NUMBER); --Oracle
--CREATE TABLE T_MAX_LHR (ARTICLE VARCHAR(30),AUTHOR VARCHAR(30),PRICE FLOAT); --MySQL oracle通用
INSERT INTO T_MAX_LHR VALUES ('0001','B',3.99);
INSERT INTO T_MAX_LHR VALUES ('0002','A',10.99);
INSERT INTO T_MAX_LHR VALUES ('0003','C',1.69);
INSERT INTO T_MAX_LHR VALUES ('0004','B',19.95);
INSERT INTO T_MAX_LHR VALUES ('0005','A',6.96);
COMMIT;
SELECT * FROM T_MAX_LHR;
在Oracle中的数据：
LHR@orclasm > SELECT * FROM T_MAX_LHR;
ARTICLE AUTHOR PRICE
-------- -------- ----------
0001 B 3.99
0002 A 10.99
0003 C 1.69
0004 B 19.95
0005 A 6.96
在MySQL中的数据：
mysql> SELECT * FROM T_MAX_LHR;
+---------+--------+-------+
| ARTICLE | AUTHOR | PRICE |
+---------+--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | B | 19.95 |
| 0005 | A | 6.96 |
+---------+--------+-------+
5 rows in set (0.00 sec)
分析数据后，正确答案应该是：

ARTICLE	AUTHOR	PRICE
0002	A	10.99
0003	C	1.69 http://www.cndba.cn/lhrbest/article/2029
0004	B	19.95

对于这个例子，很容易想到的SQL语句如下所示：
SELECT T.ARTICLE,T.AUTHOR,MAX(T.PRICE) FROM T_MAX_LHR T GROUP BY T.AUTHOR;
SELECT * FROM T_MAX_LHR T GROUP BY T.AUTHOR;
在Oracle中执行上面的SQL语句报错：
LHR@orclasm > SELECT T.ARTICLE,T.AUTHOR,MAX(T.PRICE) FROM T_MAX_LHR T GROUP BY T.AUTHOR;
SELECT T.ARTICLE,T.AUTHOR,MAX(T.PRICE) FROM T_MAX_LHR T GROUP BY T.AUTHOR
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression

LHR@orclasm > SELECT * FROM T_MAX_LHR T GROUP BY T.AUTHOR;
SELECT * FROM T_MAX_LHR T GROUP BY T.AUTHOR
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
在MySQL中执行同样的SQL语句不会报错：
mysql> select version();
+-------------------------------------------+
| version() |
+-------------------------------------------+
| 5.6.21-enterprise-commercial-advanced-log |
+-------------------------------------------+

mysql> SELECT T.ARTICLE,T.AUTHOR,MAX(T.PRICE) FROM T_MAX_LHR T GROUP BY T.AUTHOR;
+---------+--------+--------------+
| ARTICLE | AUTHOR | MAX(T.PRICE) |
+---------+--------+--------------+
| 0002 | A | 10.99 |
| 0001 | B | 19.95 |
| 0003 | C | 1.69 |
+---------+--------+--------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM T_MAX_LHR T GROUP BY T.AUTHOR;
+---------+--------+-------+
| ARTICLE | AUTHOR | PRICE |
+---------+--------+-------+
| 0002 | A | 10.99 |
| 0001 | B | 3.99 |
| 0003 | C | 1.69 |
+---------+--------+-------+
3 rows in set (0.00 sec)
虽然执行不报错，可以查询出数据，但是从结果来看数据并不是最终想要的结果，甚至数据是错乱的。下面给出几种正确的写法（在Oracle和MySQL中均可执行）：
（1）使用相关子查询
SELECT *
FROM T_MAX_LHR T
WHERE (T.AUTHOR, T.PRICE) IN (SELECT NT.AUTHOR, MAX(NT.PRICE) PRICE
FROM T_MAX_LHR NT
GROUP BY NT.AUTHOR)
ORDER BY T.ARTICLE;

SELECT *
FROM T_MAX_LHR T
WHERE T.PRICE = (SELECT MAX(NT.PRICE) PRICE
FROM T_MAX_LHR NT
WHERE T.AUTHOR = NT.AUTHOR)
ORDER BY T.ARTICLE;

（2）使用非相关子查询
SELECT T.*
FROM T_MAX_LHR T
JOIN (SELECT NT.AUTHOR, MAX(NT.PRICE) PRICE
FROM T_MAX_LHR NT
GROUP BY NT.AUTHOR) T1
ON T.AUTHOR = T1.AUTHOR
AND T.PRICE = T1.PRICE
ORDER BY T.ARTICLE;
（3）使用LEFT JOIN语句
SELECT T.*
FROM T_MAX_LHR T
LEFT OUTER JOIN T_MAX_LHR T1
ON T.AUTHOR = T1.AUTHOR
AND T.PRICE < T1.PRICE
WHERE T1.ARTICLE IS NULL
ORDER BY T.ARTICLE;
在Oracle中的执行结果：
LHR@orclasm > SELECT T.*
2 FROM T_MAX_LHR T
3 LEFT OUTER JOIN T_MAX_LHR T1
4 ON T.AUTHOR = T1.AUTHOR
5 AND T.PRICE < T1.PRICE
6 WHERE T1.ARTICLE IS NULL
7 ORDER BY T.ARTICLE;

ARTICLE AUTHOR PRICE
-------- -------- ----------
0002 A 10.99
0003 C 1.69
0004 B 19.95
在MySQL中的执行结果：
mysql> SELECT T.*
-> FROM T_MAX_LHR T
-> LEFT OUTER JOIN T_MAX_LHR T1
-> ON T.AUTHOR = T1.AUTHOR
-> AND T.PRICE < T1.PRICE
-> WHERE T1.ARTICLE IS NULL
-> ORDER BY T.ARTICLE;
+---------+--------+-------+
| ARTICLE | AUTHOR | PRICE |
+---------+--------+-------+
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | B | 19.95 |
+---------+--------+-------+
3 rows in set (0.00 sec)

真题2、Oracle和MySQL中的分组（GROUP BY）后的聚合函数分别是什么？
答案：在Oracle中，可以用WM_CONCAT函数或LISTAGG分析函数；在MySQL中可以使用GROUP_CONCAT函数。示例如下：
首先给出建表语句：
CREATE TABLE T_MAX_LHR (ARTICLE VARCHAR2(30),AUTHOR VARCHAR2(30),PRICE NUMBER); --Oracle
--CREATE TABLE T_MAX_LHR (ARTICLE VARCHAR(30),AUTHOR VARCHAR(30),PRICE FLOAT); --MySQL oracle通用
INSERT INTO T_MAX_LHR VALUES ('0001','B',3.99);
INSERT INTO T_MAX_LHR VALUES ('0002','A',10.99);
INSERT INTO T_MAX_LHR VALUES ('0003','C',1.69);
INSERT INTO T_MAX_LHR VALUES ('0004','B',19.95);
INSERT INTO T_MAX_LHR VALUES ('0005','A',6.96);
COMMIT;
SELECT * FROM T_MAX_LHR;
在MySQL中：
mysql> SELECT T.AUTHOR, GROUP_CONCAT(T.ARTICLE), GROUP_CONCAT(T.PRICE)
-> FROM T_MAX_LHR T
-> GROUP BY T.AUTHOR;
+--------+-------------------------+-----------------------+
| AUTHOR | GROUP_CONCAT(T.ARTICLE) | GROUP_CONCAT(T.PRICE) |
+--------+-------------------------+-----------------------+
| A | 0002,0005 | 10.99,6.96 |
| B | 0001,0004 | 3.99,19.95 |
| C | 0003 | 1.69 |
+--------+-------------------------+-----------------------+
3 rows in set (0.00 sec)
在Oracle中：
LHR@orclasm > SELECT T.AUTHOR, WM_CONCAT(T.ARTICLE) ARTICLE, WM_CONCAT(T.PRICE) PRICE
2 FROM T_MAX_LHR T
3 GROUP BY T.AUTHOR;

AUTHOR ARTICLE PRICE
-------- --------------- ---------------
A 0002,0005 10.99,6.96
B 0001,0004 3.99,19.95
C 0003 1.69

LHR@orclasm > SELECT T.AUTHOR,
2 LISTAGG(T.ARTICLE, ',') WITHIN GROUP(ORDER BY T.PRICE) ARTICLE,
3 LISTAGG(T.PRICE, ',') WITHIN GROUP(ORDER BY T.PRICE) PRICE
4 FROM T_MAX_LHR T
5 GROUP BY T.AUTHOR;

AUTHOR ARTICLE PRICE
-------- --------------- ---------------
A 0005,0002 6.96,10.99
B 0001,0004 3.99,19.95
C 0003 1.69

原作者不知道是谁了，这个图不是小麦苗画的。

About Me

.............................................................................................................................................

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群：230161599 微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

.............................................................................................................................................

● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。

小麦苗的微信公众号小麦苗的QQ群小麦苗的微店

.............................................................................................................................................

签到成功

CNDBA社区

Oracle和MySQL分组查询GROUP BY

Oracle和MySQL分组查询GROUP BY

小麦苗

QQ交流群

注册联系QQ