mysql group by 去重以及取同一组的第一条数据

最新推荐文章于 2024-09-19 06:04:45 发布

guoyiguang2

最新推荐文章于 2024-09-19 06:04:45 发布

阅读量1.3k

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_38807606/article/details/133036028

版权

文章讲述了在MySQL中遇到`ONLY_FULL_GROUP_BY`模式下GROUPBY语句错误的情况，介绍了三种解决方案：使用ANY_VALUE函数、临时修改sql_mode和永久修改配置文件，以处理非聚合列的问题。

摘要由CSDN通过智能技术生成





select * from  boy ;
# +--+----+--------+-----+
# |id|name|del_flag|name0|
# +--+----+--------+-----+
# |1 |慕容皝 |0       |ba   |
# |2 |慕容垂 |0       |bb   |
# |3 |慕容博 |1       |bc   |
# |4 |慕容复 |1       |bd   |
# +--+----+--------+-----+

select * from  girl ;

# +--+-----+------+-----+
# |id|name |boy_id|name0|
# +--+-----+------+-----+
# |1 |慕容吹雪1|1     |ga   |
# |3 |慕容吹雪2|3     |gc   |
# |4 |慕容吹雪3|3     |gd   |
# |5 |慕容吹雪4|3     |ge   |
# |6 |慕容吹雪5|NULL  |gf   |
# +--+-----+------+-----+


select boy.id  ,boy.name,girl.name  from  boy  inner join girl on boy.id = girl.boy_id ;

# +--+----+-----+
# |id|name|name |
# +--+----+-----+
# |1 |慕容皝 |慕容吹雪1|
# |3 |慕容博 |慕容吹雪2|
# |3 |慕容博 |慕容吹雪3|
# |3 |慕容博 |慕容吹雪4|
# +--+----+-----+


select boy.id  ,boy.name,girl.name  from  boy  inner join girl on boy.id = girl.boy_id
group by  boy.id  ,boy.name,girl.name
;

# +--+----+-----+
# |id|name|name |
# +--+----+-----+
# |1 |慕容皝 |慕容吹雪1|
# |3 |慕容博 |慕容吹雪2|
# |3 |慕容博 |慕容吹雪3|
# |3 |慕容博 |慕容吹雪4|
# +--+----+-----+

#  以下sql 会报错
# Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'niu_ke.girl.name'
# which is not functionally dependent on columns in GROUP BY clause;
# this is incompatible with sql_mode=only_full_group_by
select boy.id  ,boy.name,girl.name  from  boy  inner join girl on boy.id = girl.boy_id
group by  boy.id
;
# 查看sql_mode的语句
select @@GLOBAL.sql_mode;
# +---------------------------------------------------------------------------------------------------------------------+
# |@@GLOBAL.sql_mode                                                                                                    |
# +---------------------------------------------------------------------------------------------------------------------+
# |ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION|
# +---------------------------------------------------------------------------------------------------------------------+

# 解决方案 一：使用函数ANY_VALUE()包含报错字段

select boy.id  , boy.name ,ANY_VALUE(girl.name)   from  boy  inner join girl on boy.id = girl.boy_id
group by  boy.id ;
# ANY_VALUE 说明 只取了 任意一个  girl.name
# MySQL有any_value(field)函数，它主要的作用就是抑制ONLY_FULL_GROUP_BY值被拒绝。
# 这样sql语句不管是在ONLY_FULL_GROUP_BY模式关闭状态还是在开启模式都可以正常执行，不被mysql拒绝。
# any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据。
# +--+--------------------+--------------------+
# |id|ANY_VALUE( boy.name)|ANY_VALUE(girl.name)|
# +--+--------------------+--------------------+
# |1 |慕容皝                 |慕容吹雪1               |
# |3 |慕容博                 |慕容吹雪2               |
# +--+--------------------+--------------------+

# 解决方案二：通过sql语句暂时性修改sql_mode
SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

select boy.id  ,boy.name,girl.name  from  boy  inner join girl on boy.id = girl.boy_id
group by  boy.id
;
# +--+----+-----+
# |id|name|name |
# +--+----+-----+
# |1 |慕容皝 |慕容吹雪1|
# |3 |慕容博 |慕容吹雪2|
# +--+----+-----+

# 解决方案三：通过配置文件永久修改sql_mode 省略......