Mysql 的ANY_VALUE()函数和 ONLY_FULL_GROUP_BY 模式

1、ONLY_FULL_GROUP_BY 引发

在mysql 5.7版本以上进行一些ORDER BY 或者 GROUP BY 时，会出现如下错误

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘information_schema.PROFILING.SEQ’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

他的意思是，你既然GROUP BY 或者ORDER BY了，就需要保证你SELECT 的列都在GROUP BY 和 ORDER BY 中，而一般情况下我们只是GROUP BY 和 ORDER BY 一个，两个或者三个左右的字段，而不是全部

2、通过临时去掉ONLY_FULL_GROUP_BY 模式来进行查询

查询出来现有的模式

select @@global.sql_mode;

  

 

  
1
 

去掉里面的 ONLY_FULL_GROUP_BY 模式，然后set 回去

set @@global.sql_mode = "查出来的值，去掉 ONLY_FULL_GROUP_BY   "

  

 

  
1
 

3、通过修改配置文件去掉ONLY_FULL_GROUP_BY 模式来进行查询

查询出来现有的模式

select @@global.sql_mode;

  

 

  
1
 

找到mysql目录，找到bin 文件夹下面的 my.ini 配置文件

sql_mode="查出来的值，去掉 ONLY_FULL_GROUP_BY  "

  

 

  
1
 

然后重启Mysql

4、通过ANY_VALUE()函数忽略没有参与分组的列

没有参与分组的字段，套一个ANY_VALUE() 即可

示例代码

SELECT ANY_VALUE(DATE_FORMAT(d.gmt_create,'%Y-%m-%d')) AS `gmt_create`,
ANY_VALUE(d.pk_id) AS `pk_id`,
ANY_VALUE(d.deleted) AS `deleted`,
ANY_VALUE(d.gmt_modified) AS `gmt_modified`,
ANY_VALUE(d.remark) AS `remark`,
ANY_VALUE(d.sort) AS `sort`,
d.time_point,
ANY_VALUE((MAX(d.visit_number) - MIN(d.visit_number))) AS `visit_number`,
ANY_VALUE((MAX(d.level) - MIN(d.level))) AS `level`,
ANY_VALUE((MAX(d.integral) - MIN(d.integral))) AS `integral`,
ANY_VALUE((MAX(d.top) - MIN(d.top))) AS `top`,
ANY_VALUE((MAX(d.article_number) - MIN(d.article_number))) AS `article_number`,
ANY_VALUE((MAX(d.fans) - MIN(d.fans))) AS `fans`,
ANY_VALUE((MAX(d.like_number) - MIN(d.like_number))) AS `like_number`,
ANY_VALUE((MAX(d.comment_number) - MIN(d.comment_number))) AS `comment_number`
FROM `data` d
GROUP BY d.time_point
ORDER BY gmt_create DESC, d.time_point DESC
LIMIT ?1

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
 

文章来源: wretchant.blog.csdn.net，作者：简简单单OnlineZuozuo，版权归原作者所有，如需转载，请联系作者。

原文链接：wretchant.blog.csdn.net/article/details/102574761