mysql查询笔记

MySQL查询表内重复记录

1、查找表中多余的重复记录，重复记录是根据单个字段（name）来判断

SELECT
*
FROM
user_table_temp
WHERE
NAME IN (
SELECT
NAME
FROM
user_table_temp
GROUP BY
name
HAVING
COUNT(name) > 1
);

2、删除表中多余的记录，重复记录是根据单个字段（name）来判断

DELETE t.*
FROM
user_table_temp t,
(
SELECT
MIN(id) id,
NAME
FROM
user_table_temp
GROUP BY
NAME
HAVING
COUNT(NAME) > 1
) a
WHERE
t. NAME = a. NAME
AND t.id != a.id;