sql语句之group by分组的坑

• mysql 一般分为5.7之前和之后。因为5.7是一个大的版本的改动
• 比如在使用分组查询的时候

mysql分组中的坑
分组中select后面的列只能有2种：

出现在group by后面的列
使用聚合函数的列

也就是分组后的语句的select在5.7之后只能放使用聚合函数的列和分组的列这两种

oracle、sqlserver、db2中也是按照这种规范来的。

5.7版本，默认是按照这种规范来的。

mysql早期的一些版本，没有上面这些要求，select后面可以跟任何合法的列。

任何和分组函数一同查询的字段都要是group by后面的字段

示例

需求：获取每个用户下单的最大金额及下单的年份，输出：用户id，最大金额，年份，写法如下：
mysql> select
user_id 用户id, max(price) 最大金额, the_year 年份
FROM t_order t
GROUP BY t.user_id;
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘javacode2018.t.the_year’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

• 上面的sql报错了，原因因为the_year不符合上面说的2条规则（select后面的列必须出现在group by中或者使用聚合函数），而sql_mode限制了这种规则，我们看一下sql_mode的配置：

select @@sql_mode;
±------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
±------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
±------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

• sql_mode中包含了ONLY_FULL_GROUP_BY，这个表示select后面的列必须符合上面的说的2点规范。

• 正确的应该是：

mysql> SELECT
     user_id 用户id,
     price 最大金额,
      the_year 年份
FROM
      t_order t1
WHERE
      (t1.user_id , t1.price)
IN
      (SELECT
            t.user_id, MAX(t.price)
      FROM
            t_order t
      GROUP BY t.user_id);
2 SELECT
     user_id 用户id,
      price 最大金额,
      the_year 年份
FROM
t_order t1,(SELECT
            t.user_id uid, MAX(t.price) pc
FROM
            t_order t
            GROUP BY t.user_id) t2
WHERE
      t1.user_id = t2.uid
      AND t1.price = t2.pc;

Summary

在写分组查询的时候，最好按照标准的规范来写，select后面出现的列必须在group by中或者必须使用聚合函数。
select语法顺序：select、from、where、group by、having、order by、limit，顺序不能搞错了，否则报错。

和分组函数一同使用的列必须要在group by 后出现 ，如果未在group by 后出现这个列是不能拆和分组函数一同出现在select后