網頁

2021/3/25

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 |
+----+------+---------------+-----+


沒有留言:

張貼留言