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最高的记录（要包含所有字段）。

首先给出建表语句：

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)

分析数据后，正确答案应该是：

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