Mysql中的group by使用方法及注意事项

最近在新的项目中,使用了最新版本的 lnmp (linux+nginx+mysql+php)环境。其中Mysql的版本就是5.7, 但是在使用中,发现了很多错误,网站不能正确运行,其中报的最严重的一个错误就是:

ERROR 1055 (42000):  Expression #2 of SELECT list is not in GROUP BY clause and 
contains nonaggregated column 'col_user_6.a.START_TIME' which is not functionally 
dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

于是乎各种谷哥、度娘啊,一顿查找,原来是 mysql5.7默认开启 only_full_group_by,其中给出的解决方法是:执行SET GLOBAL sql_mode = ''; 把sql_mode 改成非only_full_group_by模式,这种方法虽然不会再报这种错误,但是这个方法是非常不妥的,我们要彻底弄明白only_full_group_by这个是什么玩意,既然Mysql5.7开启了它,就说明它有一定的作用,我们遇到问题,不能用逃避的方法来解决。

之前MySQL中的ONLY_FULL_GROUP_BY模式遵守SQL92标准的规定,在默认情况下是关闭的。MySQL并未强求严格按照SQL92标准编写查询命令。在MySQL5.7.5中,ONLY_FULL_GROUP_BY的含义改变了,由于遵守SQL99,现在将实现更为复杂的功能。也就是说,有时候要写的代码少了,却仍旧能实现查询功能。现在的 ONLY_FULL_GROUP_BY默认更加宽泛。关于SQL92和SQL99可以从下面文章中查看,或自行搜索了解。这里不作解释。

http://savage.net.au/SQL/sql-92.bnf.html

http://savage.net.au/SQL/sql-99.bnf.html

遵守SQL99之后,ONLY_FULL_GROUP_BY模式下,要查询的字段(除复合函数外)都要出现在 GROUP BY中。什么意思呢?举个例子:

我们以前用的语句如:SELECT id, name, class FROM study GROUP BY class; 就会出错,我们要换成 SELECT id, name, class FROM study GROUP BY id, name, class;

我们以前用的语句如:SELECT id, name, class, max(math) AS math FROM study GROUP BY class; 也会出错,我们要换成 SELECT id, name, class, max(math) AS math FROM study GROUP BY id, name, class;

我们发现在 select 后面的字段中除了复合函数外,都要出现在 GROUP BY 中,那这时,我们确实需要按 class 分组怎么办,下面创建一个表,来实际说明一下。

CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `name` varchar(255) NOT NULL COMMENT '姓名',
  `class` varchar(255) NOT NULL COMMENT '班级',
  `math` int(10) unsigned NOT NULL COMMENT '数学成绩',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES ('1', '张三', '1', '90');
INSERT INTO `student` VALUES ('2', '李四', '2', '89');
INSERT INTO `student` VALUES ('3', '王五', '3', '94');
INSERT INTO `student` VALUES ('4', '二八', '1', '78');
INSERT INTO `student` VALUES ('5', '三六', '2', '78');
INSERT INTO `student` VALUES ('6', '周七', '2', '93');
INSERT INTO `student` VALUES ('7', '陆三', '3', '59');

我们创建一个学生表,有5个字段:学生ID、姓名、班级、数学成绩,我们插入7条数据,现在我们要获取每个班级里成绩最高的一个学生的信息。

很多刚学Mysql的人会用: SELECT *, max(math) FROM study GROUP BY class; 来获取,也很好理解, 按class分组,查看math最大值,其实是错误的,如果在ONLY_FULL_GROUP_BY下,会出错刚开始说的那个错误提示。如果把ONLY_FULL_GROUP_BY给关了或版本太小的mysql下,不会出错,但是,返回的信息是错的。返回信息出下图

1.png

我们发现,学生的信息和math最大值是对不上的,不知道有没有在意这个问题呢???重点来了,我们怎么来获取自己想要的信息呢。这也是本文的标题所说, group by的正确使用方法:我们可以按下面的思路来一步一步实现:

1、我们获取所有的学生信息,并给每个学生添加一个字段,即本班级中最高的成绩。

SELECT *, (SELECT MAX(math) from study WHERE class=w.class) as maxmath
 FROM study AS w;

2.png

2、用 HAVING 来过滤,每一行中 math 小于 maxmath的,然后搞定

SELECT *, (SELECT MAX(math) from study WHERE class=w.class) as maxmath
 FROM study AS w HAVING math = maxmath;

3.png

当然,也可以把HAVING中的判断放到 WHERE 中,如下语句:

SELECT *  FROM study AS w WHERE math = (SELECT MAX(math) from study
 WHERE class=w.class);

这条语句返回的数据和上面的图一样。也是正确的。

这里只是做一个笔记,因为这样的查询在项目中,很常见,比如:查看商品表中,每个分类下的商品销售最高的,等等,希望下次再遇到这样的问题,不会再出错。

未经允许不得转载:易读小屋  »  Mysql中的group by使用方法及注意事项