mysql 查询及删除重复记录，并保存最小记录

查询重复记录

SELECT
	*
FROM
	tp_pool_company
WHERE
	company_no IN (
		SELECT
			company_no
		FROM
			tp_pool_company
		GROUP BY
			company_no
		HAVING
			COUNT(1) > 1
	)
AND id NOT IN (
	SELECT
		MIN(id)
	FROM
		tp_pool_company
	GROUP BY
		company_no
	HAVING
		COUNT(1) > 1
)
and company_no<>"";

 删除重复记录

DELETE
FROM
	tp_pool_company
WHERE
	company_no IN (
		SELECT
			t.company_no
		FROM
			(
				SELECT
					company_no
				FROM
					tp_pool_company
				GROUP BY
					company_no
				HAVING
					count(1) > 1
			) t
	)
AND id NOT IN (
SELECT
	dt.minid
FROM
	(
		SELECT
			min(id) AS minid
		FROM
			tp_pool_company
		GROUP BY
			company_no
		HAVING
			count(1) > 1
	) dt
)
and company_no<>""