Oracle 找出重複資料

在 Oracle 資料庫中要找出重複資料可以使用下列方式：

假設要尋找的資料表名稱叫做 MEMBER, 要尋找重複的欄位叫做 NAME

查詢語法：

  
SELECT count(NAME), NAME
FROM MEMBER
GROUP BY NAME
HAVING count(NAME) > 1 ;
  

如果要查詢兩個欄位合併是否依然有重複，可以使用「||」符號串接：

  
SELECT count(NAME || AGE), NAME || AGE
FROM MEMBER
GROUP BY NAME || AGE
HAVING count(NAME || AGE) > 1 ;
  

範例輸出：

  
SELECT count(NAME), NAME
FROM MEMBER
GROUP BY NAME
HAVING count(NAME) > 1 ;

+-------------------+
|count(NAME) | NAME |
+-------------------+
|2           | 小明  |
|5           | 大頭  |
|3           | 老王  |
+-------------------+