您现在的位置是:首页 >技术教程 >MySQL多列字段去重的案例实践网站首页技术教程
MySQL多列字段去重的案例实践
同事提了个需求,如下测试表,有code、cdate和ctotal三列,
select * from tt;
现在要得到code的唯一值,但同时带着cdate和ctotal两个字段。
提起"唯一值",想到的就是distinct。distinct关键字可以过滤多余的重复记录只保留一条。
distinct支持单列去重和多列去重,如果是单列去重,简明易懂,即相同值只保留1个,如下所示,
select distinct code from tt;
多列去重则是根据指定的去重列信息进行,即只有所有指定的列信息都相同,才会被认为是重复的信息,如下所示,code、cdate和ctotal都相同,才会返回记录,因此不是字面上的理解,即只要code是distinct的,cdate和ctotal无需关注。实际上当distinct应用到多个字段的时候,其应用的范围是其后面的所有字段,而不只是紧贴着它的一个字段,即distinct同时作用了三个字段,code、cdate和ctotal,并不只是code字段,
select distinct code, cdate, ctotal from tt;
而且distinct只能放到所有字段的前面,如果像这种,distinct之前有其它字段,则会提示错误,
select cdate, ctotal, distinct code from tt;
SQL 错误 [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct code from tt' at line 1
但是如上SQL使用distinct关键字,并没有满足需求,即得到code的唯一值,但同时带着cdate和ctotal两个字段,可以看到有很多相同的code。
除了distinct,group by子句也可以去重,从需求的理解上,如果按照code做group by,应该就可以得到唯一的code了,但是实际执行,提示这个错误,
select code, cdate, ctotal from tt group by code;
SQL 错误 [1055] [42000]: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.tt.code' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
很常见的错误,因为sql_mode中含only_full_group_by规则,
show variables like '%sql_mode%';
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
only_full_group_by规则是指对group by进行查询的SQL,不允许select部分出现group by中未出现的字段,也就是select查询的字段必须是group by中出现的或者使用聚合函数的,即校验更加严格。
P.S. MySQL不同版本sql_mode默认值可能是不同的,因此在数据库升级配合的应用迁移过程中,尤其要注意像only_full_group_by这种校验规则的改变,很可能是个坑。
仅针对当前这个问题,可以在会话级,修改sql_mode,调整校验的强度,删除only_full_group_by,
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
此时,使用group by,
select code, cdate, ctotal from tt group by code;
就可以得到想要的效果了,
除了group by,还可以使用group_concat函数,配合distinct,达到相同效果。
我们分解来做,可以看到group_concat(code),得到的是所有记录的code值拼接成新字段,
select group_concat(code), cdate, ctotal from tt group by code;
group_concat中加上distinct,就可以过滤所有的重复值,满足了需求,
select group_concat(distinct code), cdate, ctotal from tt group by code;
当然,这种在会话级通过改动sql_mode实现的路径,还需要考虑场景,因为缺少only_full_group_by的校验,按照code聚类了,但cdate和ctotal的值很可能是不唯一的,返回的结果,只能准确描述code的数据情况,不能代表cdate和ctotal的真实数据情况。因此,任何方案的选择,都需要结合实际的场景需求,我们找的方案,不一定是最好的,但需要最合适的。
本文关键字:#SQL# #去重#