代码敲得再六又怎么样...

MySQL删除重复数据并只保留一条数据

150 2020-12-16 07:15:06

单字段操作

第一种方法

DELETE
FROM
	tablename
WHERE
	fieldname IN (
		SELECT
			t.dname
		FROM
			(
				SELECT
					fieldname
				FROM
					tablename
				GROUP BY
					fieldname
				HAVING
					count(1) > 1
			) t
	)
AND fieldname NOT IN (
SELECT
	dt.minfieldname
FROM
	(
		SELECT
			min(fieldname) AS minfieldname
		FROM
			fieldname
		GROUP BY
			fieldname
		HAVING
			count(1) > 1
	) dt
)

第二种方法

DELETE
FROM
	tablename
WHERE
	fieldname NOT IN (
		SELECT
			dt.minfieldname
		FROM
			(
				SELECT
					MIN(fieldname) AS minfieldname
				FROM
					tablename
				GROUP BY
					fieldname
			) dt
	)

多字段操作

DELETE
FROM
	tablename
WHERE
	(fieldname_1, fieldname_2) IN (
		SELECT
			t.fieldname_1,
			t.fieldname_2
		FROM
			(
				SELECT
					fieldname_1,
					fieldname_2
				FROM
					tabledname
				GROUP BY
					fieldname_1,
					fieldname_2
				HAVING
					count(1) > 1
			) t
	)
AND fieldname_1 NOT IN (
	SELECT
		dt.minfieldname_1
	FROM
		(
			SELECT
				min(fieldname_1) AS minfieldname_1
			FROM
				tablename
			GROUP BY
				fieldname_1,
				fieldname_2
			HAVING
				count(1) > 1
		) dt
)