解决Mysql分组查询语句Expression #1 of SELECT list is not in GROUP BY clause ... ... 替换方案

前言

mysql的分组查询算是日常开发中用的最频繁的语句之一。

问题发现

公司有个需求，需要用到分组查询，本以为很简单的sql语句，硬生生的弄了几个小时，特此记录下来。

以某数据库查询为例，sql语句如下：

SELECT * FROM `customer` GROUP BY customer.sex

返回错误信息

SELECT * FROM `customer` GROUP BY customer.sex
> 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 
'member.customer.member_no' which is not functionally dependent on columns in GROUP BY clause; 
 this is incompatible with sql_mode=only_full_group_by
> 时间: 0.078s

似乎只能这样做：

SELECT sex FROM `customer` GROUP BY customer.sex

这种查询只能查对应一个字段，显然不满足产品需求。这是因为使用GROUP BY 语句违背了sql_mode=only_full_group_by。因为mysql版本5.7之后默认的模式是ONLY_FULL_GROUP_BY。

如果启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下），MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列，也不在功能上依赖于它们。

看了网上很多办法，有修改全局配置的，或者换mysql版本的，这些方案似乎可以解决部分问题，但是如果项目已经生产使用了，如果再来操作这些，改动有点太大了，既然都行不通，那就用sql语句的方式让他行得通。

问题解决

假设：现有需求查询会员最新的登陆记录。

表结构如下：

用户表

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `auth` varchar(255) DEFAULT NULL COMMENT '最高权限用户=admin',
  `name` varchar(11) NOT NULL COMMENT '用户名',
  `pwd` varchar(255) DEFAULT NULL COMMENT '密码',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `status` int NOT NULL COMMENT '0为不启用,1为启用',
  `role_id` int DEFAULT NULL COMMENT '用户对应角色,对应role表id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`) COMMENT '登录名'
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

表数据

操作日志表

CREATE TABLE `log_login` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  `operate_name` varchar(255) DEFAULT NULL,
  `ctime` datetime DEFAULT NULL,
  `utime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

表数据

方案一：再有主键的情况下，通过max()函数获取最新的id进行查询

在有主键的情况下：

• 先通过分组user_id。
• 再使用max()函数获取每个用户的主键id，一般情况下主键id应该都是递增的形式。
• 再把查询的每个用户的主键id，当做查询条件带入到第二条sql语句中，这样一来就可以获取到完整的一条数据，也可以和其他表关联查询。

示例代码如下：

SELECT
	log_login.*,
	user.*
FROM
	log_login
	INNER JOIN `USER` ON log_login.user_id = `user`.id 
WHERE
	log_login.id IN ( SELECT max( id ) AS id FROM log_login GROUP BY user_id )

查询结果如图：

方案二：没有主键的情况下，通过ctime倒排取第一条数据进行查询

没有主键的情况下，最少也要满足两个条件：

1. 有可以倒排的字段（比如：ctime创建时间）。
2. 有唯一字段。

假设现在这个表结构没有主键了，id当作唯一字段。

• 先通过ctime倒排，当做第二条sql语句的子查询，子查询只能查询一行数据，所以加个limit限制一条，然后查询条件将子查询的user_id与第二条sql语句的user_id匹配，这样每个user_id对应最新时间的唯一id（注：多个相同user_id会有多条数据，但都是最新唯一id），
• 然后把第二个查询语句，通过user_id进行分组，得到每个用户最新时间段内的唯一id（去除重复user_id的多条数据），再把每个用户的唯一id当做查询条件带入到第三条sql语句中，这样一来就可以获取到完整的一条数据，也可以和其他表关联查询，示例代码如下：

SELECT
	log_login.*,
	USER.* 
FROM
	log_login
	INNER JOIN `USER` ON log_login.user_id = `user`.id 
WHERE
	log_login.id IN (
	SELECT
		( SELECT id FROM log_login WHERE log_login.user_id = log.user_id ORDER BY ctime DESC LIMIT 0, 1 ) AS id 
	FROM
		log_login log 
	GROUP BY
	log.user_id 
	)

查询结果如图：