在 MySQL 表中查詢重複記錄
-
使用
SELECT DISTINCT語句檢查 MySQL 表中的重複項 -
使用
SELECT COUNT語句查詢 MySQL 表中重複出現的次數 -
使用
INNER JOIN語句檢視 MySQL 表中的重複記錄
本教程探討如何分別使用 SELECT DISTINCT 和 SELECT COUNT 語句檢查 MySQL 表中的重複項並檢索重複項的數量。
使用 SELECT DISTINCT 語句檢查 MySQL 表中的重複項
資料庫表中有重複記錄的影響可能從輕微的不便到災難。幸運的是，MySQL 有一些漂亮的關鍵字可以組合起來掃描表中的重複項。
此外，我們可以計算重複記錄的出現次數，並在必要時將其刪除。
讓我們使用名為 employee_details 的示例表建立一個 test_company 資料庫。
CREATE DATABASE test_company;
USE test_company;
CREATE TABLE employees_details(
id INT AUTO_INCREMENT,
name VARCHAR (255) NOT NULL,
title VARCHAR(255) NOT NULL,
salary INT,
PRIMARY KEY (id)
);
該表填充了值，包括重複值。
INSERT INTO employees_details (name, title, salary) Values
('James Maddison','Computer Engineer',80000),
('Matthew Defoe','Software Architect',150000),
('Daniel Jameson','Software Engineer II', 95000),
('Jules Reddington','Senior Software Engineer',120000),
('Carlos Rodriguez','Data Engineer',100000),
('Matthew Defoe','Software Architect',150000),
('Daniel Jameson','Software Engineer II', 95000),
('Jules Reddington','Senior Software Engineer',120000);
SELECT * FROM employees_details;
輸出：
+----+------------------+--------------------------+--------+
| id | name | title | salary |
+----+------------------+--------------------------+--------+
| 1 | James Maddison | Computer Engineer | 80000 |
| 2 | Matthew Defoe | Software Architect | 150000 |
| 3 | Daniel Jameson | Software Engineer II | 95000 |
| 4 | Jules Reddington | Senior Software Engineer | 120000 |
| 5 | Carlos Rodriguez | Data Engineer | 100000 |
| 6 | Matthew Defoe | Software Architect | 150000 |
| 7 | Daniel Jameson | Software Engineer II | 95000 |
| 8 | Jules Reddington | Senior Software Engineer | 120000 |
+----+------------------+--------------------------+--------+
8 rows in set (0.00 sec)
此表中有三個重複項，由於表的大小較小，因此很容易發現。我們對較大的表使用 SELECT DISTINCT 語句從表中檢索唯一記錄。
根據官方文件，SELECT DISTINCT 語句僅檢索輸出記錄的一個例項，即使它出現多次。
因此，當 SELECT DISTINCT 語句返回的記錄數小於表中的總記錄數時，我們可以確定存在重複。
-- Retrieving only distinct records.
SELECT DISTINCT name,title,salary FROM employees_details;
輸出：
+------------------+--------------------------+--------+
| name | title | salary |
+------------------+--------------------------+--------+
| James Maddison | Computer Engineer | 80000 |
| Matthew Defoe | Software Architect | 150000 |
| Daniel Jameson | Software Engineer II | 95000 |
| Jules Reddington | Senior Software Engineer | 120000 |
| Carlos Rodriguez | Data Engineer | 100000 |
+------------------+--------------------------+--------+
5 rows in set (0.00 sec)
請注意，查詢中不包含 id 列，因為 MySQL 將 id 列標識為唯一記錄。
因此，在查詢中包含 id 列將返回所有記錄（包括重複項）作為唯一記錄。
從結果集中，我們可以推斷出有三 (3) 條重複記錄，因為查詢返回五 (5) 條不同的記錄並且表中有八 (8) 條記錄。
使用 SELECT COUNT 語句查詢 MySQL 表中重複出現的次數
現在，在檢測到重複項的存在後，我們可以使用 SELECT COUNT 語句來查詢重複項的出現次數。
SELECT name AS 'employee name', COUNT(*) AS Occurrence FROM employees_details
GROUP BY name
HAVING Occurrence > 1;
輸出：
+------------------+------------+
| employee name | Occurrence |
+------------------+------------+
| Daniel Jameson | 2 |
| Jules Reddington | 2 |
| Matthew Defoe | 2 |
+------------------+------------+
3 rows in set (0.001 sec)
這將檢索重複的記錄和表中每條記錄的重複數。正如預期的那樣，有三 (3) 條重複記錄。
SELECT COUNT 語句的使用將通過此官方參考進一步討論。
使用 INNER JOIN 語句檢視 MySQL 表中的重複記錄
我們可以在目標表和 SELECT DISTINCT 查詢之間使用 INNER JOIN 查詢來檢視主記錄旁邊的重複記錄。
SELECT I.id, O.name, O.title, O.salary
FROM employees_details AS I
INNER JOIN(SELECT DISTINCT name, title, salary FROM employees_details) AS O
ON I.name = O.name;
輸出：
+----+------------------+--------------------------+--------+
| id | name | title | salary |
+----+------------------+--------------------------+--------+
| 1 | James Maddison | Computer Engineer | 80000 |
| 2 | Matthew Defoe | Software Architect | 150000 |
| 6 | Matthew Defoe | Software Architect | 150000 |
| 3 | Daniel Jameson | Software Engineer II | 95000 |
| 7 | Daniel Jameson | Software Engineer II | 95000 |
| 4 | Jules Reddington | Senior Software Engineer | 120000 |
| 8 | Jules Reddington | Senior Software Engineer | 120000 |
| 5 | Carlos Rodriguez | Data Engineer | 100000 |
+----+------------------+--------------------------+--------+
8 rows in set (0.001 sec)
Victor is an experienced Python Developer, Machine Learning Engineer and Technical Writer with interests across various fields of science and engineering. He is passionate about learning new technologies and skill and working on challenging problems. He enjoys teaching, intellectual discourse, and gaming, among other things.
LinkedIn GitHub