數(shù)據(jù)庫(kù)中有個(gè)大表，需要查找其中的名字有重復(fù)的記錄id，以便比較。
如果僅僅是查找數(shù)據(jù)庫(kù)中name不重復(fù)的字段，很容易

復(fù)制代碼 代碼如下:

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

復(fù)制代碼 代碼如下:

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

復(fù)制代碼 代碼如下:

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

復(fù)制代碼 代碼如下:

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

復(fù)制代碼 代碼如下:

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

復(fù)制代碼 代碼如下:

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