mysql千万数据查重_mysql查重 去除重复数据

1.根据one列查询重复的数据(根据单列判断重复)

SELECT * FROM testdelete

WHERE ONE IN (SELECT ONE FROM testdelete GROUP BY ONE HAVING COUNT(ONE) > 1)

2.删除表中的重复记录:(根据单列删除且保留ID最小的一条) 错误：不能直接在查询的语句中进行操作

-- 错误语句 错误：不能直接在查询的语句中进行操作

DELETE

FROM testdelete

WHERE ONE IN(SELECT

ONE

FROM testdelete

GROUP BY ONE

HAVING COUNT(ONE) > 1)

AND id NOT IN(SELECT

MIN(id)

FROM testdelete

GROUP BY ONE

HAVING COUNT(ONE) > 1)

解决办法:将查询包装一层:

DELETE

FROM testdelete

WHERE ONE IN(SELECT

ONE

FROM (SELECT

ONE

FROM testdelete

GROUP BY ONE

HAVING COUNT(ONE) > 1) a)

AND id NOT IN(SELECT

*

FROM (SELECT

MIN(id)

FROM testdelete

GROUP BY ONE

HAVING COUNT(ONE) > 1) b)

3.根据one,two,three判断重复:(根据单多判断重复)

SELECT * FROM testdelete a

WHERE (a.one,a.two,a.three) IN (SELECT ONE,two,three FROM testdelete GROUP BY ONE,two,three HAVING COUNT(*) > 1)

4.删除表中的重复数据(根据多列进行删除且保留ID最小的一条)

DELETE

FROM testdelete

WHERE (ONE,two,three)IN(SELECT

ONE,

two,

three

FROM (SELECT

ONE,

two,

three

FROM testdelete

GROUP BY ONE,two,three

HAVING COUNT( * ) > 1) a)

AND id NOT IN(SELECT

MIN(id)

FROM (SELECT

MIN(id) AS id

FROM testdelete

GROUP BY ONE,two,three

HAVING COUNT( * ) > 1) b)

5. 查找表中多余的重复记录(多个字段)，不包含id最小的记录 (根据多个字段查重复不包含id最小的)

SELECT *

FROM testdelete a

WHERE (a.one,a.two,a.three)IN(SELECT

ONE,

two,

three

FROM testdelete

GROUP BY ONE,two,three

HAVING COUNT( * ) > 1)

AND id NOT IN(SELECT

MIN(id) AS id

FROM testdelete

GROUP BY ONE,two,three

HAVING COUNT( * ) > 1)

本文地址：https://blog.csdn.net/houhj168/article/details/107321331

如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！