方便演示，我们创建一个名为contacts表，其中包含四个列：id，first_name，last_name和email。
表中数据如下
| id | first_name | last_name | email | +----+------------+-----------------+--------------------------------+ | 1 | Carine | Schmitt | carine.schmitt@qq.com | | 2 | Jean | King | jean.king@yiibai.com | | 3 | Peter | Ferguson | peter.ferguson@google.com | | 4 | Janine | Labrune | janine.labrune@aol.com | | 5 | Jonas | Bergulfsen | jonas.bergulfsen@mac.com | | 6 | Janine | Labrune | janine.labrune@aol.com | | 7 | Susan | Nelson | susan.nelson@qq.com | | 8 | Zbyszek | Piestrzeniewicz | zbyszek.piestrzeniewicz@qq.com | | 9 | Roland | Keitel | roland.keitel@yahoo.com | | 10 | Julie | Murphy | julie.murphy@yahoo.com | | 11 | Kwai | Lee | kwai.lee@google.com | | 12 | Jean | King | jean.king@qq.com | | 13 | Susan | Nelson | susan.nelson@qq.comt | | 14 | Roland | Keitel | roland.keitel@yahoo.com
在一列中找到重复的值
1 SELECT 2 email, 3 COUNT(email) 4 FROM 5 contacts 6 GROUP BY email 7 HAVING COUNT(email) > 1;
输出如下
+-------------------------+--------------+ | email | COUNT(email) | +-------------------------+--------------+ | janine.labrune@aol.com | 2 | | roland.keitel@yahoo.com | 2 | +-------------------------+--------------+ 2 rows in set
在多个列中查找重复值
1 SELECT 2 first_name, COUNT(first_name), 3 last_name, COUNT(last_name), 4 email, COUNT(email) 5 FROM 6 contacts 7 GROUP BY 8 first_name , 9 last_name , 10 email 11 HAVING COUNT(first_name) > 1 12 AND COUNT(last_name) > 1 13 AND COUNT(email) > 1;
输出
+------------+-------------------+-----------+------------------+-------------------------+--------------+ | first_name | COUNT(first_name) | last_name | COUNT(last_name) | email | COUNT(email) | +------------+-------------------+-----------+------------------+-------------------------+--------------+ | Janine | 2 | Labrune | 2 | janine.labrune@aol.com | 2 | | Roland | 2 | Keitel | 2 | roland.keitel@yahoo.com | 2 | +------------+-------------------+-----------+------------------+-------------------------+--------------+ 2 rows in set