SQL 查詢某欄位是否有重覆 query column has duplicate values

SQL查詢欄位值是否有重覆的語法如下。

例如EMPLOYEE有以下欄位。

1. ID
2. NAME
3. EMAIL
4. AGE

+----+------+---------------+-----+
| ID | NAME |     EMAIL     | AGE |
+----+------+---------------+-----+
|  1 | John | john@abc.com  |  56 |
|  2 | Mary | mary@abc.com  |  42 |
|  3 | Lena | lena@abc.com  |  25 |
|  4 | John | john2@abc.com |  22 |
+----+------+---------------+-----+

則查詢NAME欄位值是否有重複的SQL為：

select NAME, count(*) from EMPLOYEE
group by NAME
having count(*) > 1;

查詢結果。

+------+----------+
| NAME | COUNT(*) |
+------+----------+
| John |        2 |
+------+----------+

查詢NAME欄位有重覆的每一筆資料的SQL為：

select a.*
from EMPLOYEE a
join (
    select NAME, count(*)
    from EMPLOYEE 
    group by NAME
    having count(*) > 1 
) b
on a.NAME = b.NAME;

查詢結果。

+----+------+---------------+-----+
| ID | NAME |     EMAIL     | AGE |
+----+------+---------------+-----+
|  1 | John | john@abc.com  |  56 |
|  4 | John | john2@abc.com |  22 |
+----+------+---------------+-----+

• How to Find Duplicate Values in a SQL Table