MySQL大表中重复字段的高效率查询方法

数据库中有个大表，需要查找其中的名字有重复的记录id，以便比较。
如果仅仅是查找数据库中name不重复的字段，很容易

复制代码 代码如下:

SELECT min(`id`),`name`
FROM `table`
GROUP BY `name`;

复制代码 代码如下:

SELECT `name`,count(`name`) as count
FROM `table`
GROUP BY `name` HAVING count(`name`) >1
ORDER BY count DESC;

复制代码 代码如下:

SELECT `id`,`name`
FROM `table`
WHERE `name` in (
SELECT `name`
FROM `table`
GROUP BY `name` HAVING count(`name`) >1
);

复制代码 代码如下:

create table `tmptable` as (
SELECT `name`
FROM `table`
GROUP BY `name` HAVING count(`name`) >1
);

复制代码 代码如下:

SELECT a.`id`, a.`name`
FROM `table` a, `tmptable` t
WHERE a.`name` = t.`name`;

复制代码 代码如下:

SELECT distinct a.`id`, a.`name`
FROM `table` a, `tmptable` t
WHERE a.`name` = t.`name`;