SQL查詢欄位值是否有重覆的語法如下。
例如EMPLOYEE
有以下欄位。
ID
NAME
EMAIL
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 |
+----+------+---------------+-----+
沒有留言:
張貼留言