mysql的分组和oracle,Oracle和MySQL分组查询GROUP BY

对于这个例子，很容易想到的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)

真题二、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