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
|
A
|
6.96
|
首先给出建表语句:
CREATE TABLE T_MAX_LHR(ARTICLE VARCHAR2(30),AUTHOR VARCHAR2(30),PRICE NUMBER);--Oracte
--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中的数据:
在MySQL中的数据:
分析数据后,正确答案应该是
ARTICLE
|
AUTHOR
|
PRICE
|
0002
|
A
|
10.99
|
0003
|
C
|
1.69
|
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语句报错:
在MySQL中执行同样的SQL语句不会报错:
虽然执行不报错,可以查询出数据,但是从结果来看数据并不是最终想要的结果,甚至数据是错乱的。下面给出几种正确的写法(在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_LHRT
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_LHRT
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 TLEFT 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中的执行结果:
在MySQL中的执行结果: