MySQL5.7之后,SQL_MODE中ONLY_FULL_GROUP_BY模式默认设置为开启状态。
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义,简单的说来,在此模式下,target list中的值要么是来自于聚合函数(sum、avg、max等)的结果,要么是来自于group by list中的表达式的值。
MySQL提供了any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝,下面来看下这个函数的具体作用。
1、在mysql中建表并插入测试数据:
create table test_any_value (
id int(10),
name varchar(10)
);
insert into test_any_value values(1,'aaa');
insert into test_any_value values(1,'bbb');
insert into test_any_value values(2,'ccc');
insert into test_any_value values(2,'ddd');
insert into test_any_value values(3,'eee');
参数ONLY_FULL_GROUP_BY默认开启时,若select列表中的字段与group by后的字段不全相同,则会报错:
然后使用any_value函数看下具体效果:
select id,any_value(name) from test_any_value group by id;
从图中可以看出,any_value函数在ONLY_FULL_GROUP_BY模式开启的情况下,按照id分组后,若每个分组对应多条数据,则会自动选择展示每个分组的第一条数据。
2、在xugu中建表并插入测试数据:
create table test_any_value(id int,name varchar);
insert into test_any_value values(1,'aaa');
insert into test_any_value values(1,'bbb');
insert into test_any_value values(2,'ccc');
insert into test_any_value values(2,'ddd');
insert into test_any_value values(3,'eee');
xugu暂不支持any_value函数:
下面使用开窗函数实现相同效果:
select id,name from (select id,name,row_number() over (partition by id order by id )rn from test_any_value)t where t.rn = 1;
这种写法是根据id分组并排序后,选择每组排序后的第一条数据,效果近似与any_value,实际情况下根据需要可选择增减排序条件。
参考:https://blog.csdn.net/Peacock__/article/details/90608246