1滓走、查詢重復(fù)記錄
select user_name,count(*) as count from user_table group by user_name having count>1;
select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2、統(tǒng)計重復(fù)數(shù)據(jù)
以下我們將統(tǒng)計表中 first_name 和 last_name的重復(fù)記錄數(shù)：
mysql> SELECT COUNT(*) as repetitions, last_name, first_name
-> FROM person_tbl
-> GROUP BY last_name, first_name
-> HAVING repetitions > 1;
以上查詢語句將返回 person_tbl 表中重復(fù)的記錄數(shù)帽馋。 一般情況下搅方，查詢重復(fù)的值，請執(zhí)行以下操作：
（1）確定哪一列包含的值可能會重復(fù)绽族。
（2）在列選擇列表使用COUNT(*)列出的那些列姨涡。
（3）在GROUP BY子句中列出的列。
（4）HAVING子句設(shè)置重復(fù)數(shù)大于1项秉。
3绣溜、過濾重復(fù)數(shù)據(jù)
如果你需要讀取不重復(fù)的數(shù)據(jù)可以在 SELECT 語句中使用 DISTINCT 關(guān)鍵字來過濾重復(fù)數(shù)據(jù)。
mysql> SELECT DISTINCT last_name, first_name
-> FROM person_tbl
-> ORDER BY last_name;
你也可以使用 GROUP BY 來讀取數(shù)據(jù)表中不重復(fù)的數(shù)據(jù)：
mysql> SELECT last_name, first_name
-> FROM person_tbl
-> GROUP BY (last_name, first_name);
