SQL查询/删除重复记录

最新推荐文章于 2024-04-29 19:28:06 发布

路_遥

最新推荐文章于 2024-04-29 19:28:06 发布

阅读量1.7k

点赞数

分类专栏： SQL 文章标签： sql

本文链接：https://blog.csdn.net/shuangrenyu5/article/details/117531770

版权

SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1、某个字段重复，根据该字段去除重复值返回唯一值。

select distinct('字段名') from '表名'

2、查找表中全部的重复记录，根据单个字段判断重复记录

select * from '表名' t
where '字段名' in (select '字段名' from '表名' group by '字段名' having count('字段名')>1)

3、查找表中重复记录（只保留 ID 最大的一条记录）

select * from '表名' t
where id in (select max(id) from '表名' group by '字段名')

4、删除表中多余的重复记录，重复记录根据单个字段判断，只保留 ID 最大的记录

delete from '表名' t
where '字段名' in (select '字段名' from '表名' group by '字段名' having count('字段名') > 1)
and id not in (select max(id) from '表名' group by '字段名' having count('字段名')>1)

5、查找表中多余的重复记录，根据多个字段判断重复记录

select * from '表名' t
where ('字段1','字段2') in (select '字段1','字段2' from '表名' group by '字段1','字段2' having count(*) > 1)

6、删除表中多余的重复记录，重复记录根据多个字段判断，只保留 ID 最大的记录

delete from '表名' t
where ('字段1','字段2') in (select '字段1','字段2' from '表名' group by '字段1','字段2' having count(*) > 1)
and id not in (select max(id) from '表名' group by '字段1','字段2' having count(*)>1)