答案：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 https://www.cndba.cn/lhrbest/article/2029

AUTHOR

PRICE

0003

https://www.cndba.cn/lhrbest/article/2029

C

1.69 https://www.cndba.cn/lhrbest/article/2029

0004

B

19.95

0005

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 https://www.cndba.cn/lhrbest/article/2029

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中執(zhí)行上面的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中執(zhí)行同樣的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)

雖然執(zhí)行不報錯，可以查詢出數據，但是從結果來看數據并不是最終想要的結果，甚至數據是錯亂的。下面給出幾種正確的寫法(在Oracle和MySQL中均可執(zhí)行)：

(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中的執(zhí)行結果：

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中的執(zhí)行結果：

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

原作者不知道是誰了，這個圖不是小麥苗畫的。

總結

以上是生活随笔為你收集整理的oracle和mysql查询_Oracle和MySQL分组查询GROUP BY的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。