从牛客SQL67解读 “为什么mysql5.7默认使用only_full_group_by”

果然事件才是检验真理的唯一标准

题目链接

当初我写这道题时就准备了两种思路，常规思路和窗口函数两种。

窗口函数必然是能解的，我尝试了一下常规思路，在这个过程中我忽然明白了为什么mysql默认的sql_mode中要有一条“only_full_group_by”了

 

目录

• 什么是only_full_group_by
• 尝试常规思路的过程
• 窗口函数

 

什么是only_full_group_by

1. order by后面的列必须是在select后面存在的

2. select、having或order by后面存在的非聚合列必须全部在group by中存在

from:MySql报错only_full_group_by的解决办法 - 知乎 (zhihu.com)

 

尝试常规思路的过程

# 常规解法
select user.name as u_n, client.name as c_n, max(date) as date
from login left join user on login.user_id=user.id
    left join client on login.client_id=client.id
group by user_id
order by u_n;

# 预期输出：fh|ios|2020-10-13 wangchao|ios|2020-10-13
# 实际输出：fh|pc|2020-10-13 wangchao|ios|2020-10-13

这应该就是为什么mysql8默认使用sql_mode=only_full_group_by了，

在select中如果有group by之外的非聚合字段，当group by按其后的字段进行聚合时，就有可能发生错误

# 修改groupp by字段为user_id,client_id之后，就有如下结果
# select user.name as u_n, client.name as c_n, max(date) as date
# from login left join user on login.user_id=user.id
#     left join client on login.client_id=client.id
# group by user_id,client_id
# order by u_n;
# fh|pc|2020-10-12
# fh|ios|2020-10-13
# wangchao|ios|2020-10-13
# 这证实了是group by 聚合的错误

 

虽然讨论区和题解中有常规思路的解法，但学习之后总觉常规解法甚是繁琐，还是窗口函数来的简洁直观：

 

窗口函数

# 窗口函数，使用row_number添加辅助列
# 先创建临时表
# select user.name as u_n, client.name as c_n, date, row_number() over(partition by user_id order by date desc) as ranking
# from login left join user on login.user_id=user.id left join client on login.client_id=client.id;
# fh|ios|2020-10-13|1
# fh|pc|2020-10-12|2
# wangchao|ios|2020-10-13|1
# wangchao|ios|2020-10-12|2

# 然后选择我们需要的
select u_n, c_n, DATE
from(
    select user.name as u_n, client.name as c_n, date, row_number() over(partition by user_id order by date desc) as ranking
    from login left join user on login.user_id=user.id left join client on login.client_id=client.id
    ) as t
where ranking = 1
order by u_n;