การใช้ GROUP BY ... HAVING COUNT(*) > 1
จัดกลุ่มตามหลายคอลัมน์ แล้วกรองเฉพาะกลุ่มที่มีมากกว่า 1 แถว
รูปแบบคำสั่ง
SELECT yourColumnName1, yourColumnName2, ..., COUNT(*) AS anyVariableName FROM yourTableName GROUP BY yourColumnName1, yourColumnName2, ... HAVING COUNT(*) > 1;
ตัวอย่าง: ตรวจสอบชื่อ-นามสกุลซ้ำ
mysql> CREATE TABLE DuplicateDemo (
-> StudentId INT NOT NULL,
-> StudentFirstName VARCHAR(100),
-> StudentLastName VARCHAR(100),
-> PRIMARY KEY(StudentId)
-> );
Query OK, 0 rows affected (0.50 sec)
mysql> INSERT INTO DuplicateDemo VALUES(1,'John','Smith');
mysql> INSERT INTO DuplicateDemo VALUES(2,'Mike','Jones');
mysql> INSERT INTO DuplicateDemo VALUES(3,'David','Smith');
mysql> INSERT INTO DuplicateDemo VALUES(4,'Carol','Taylor');
mysql> INSERT INTO DuplicateDemo VALUES(5,'David','Smith');
mysql> INSERT INTO DuplicateDemo VALUES(6,'John','Smith');
mysql> INSERT INTO DuplicateDemo VALUES(7,'John','Taylor');
ข้อมูลทั้งหมด
mysql> SELECT * FROM DuplicateDemo;
+-----------+------------------+-----------------+ | StudentId | StudentFirstName | StudentLastName | +-----------+------------------+-----------------+ | 1 | John | Smith | | 2 | Mike | Jones | | 3 | David | Smith | | 4 | Carol | Taylor | | 5 | David | Smith | | 6 | John | Smith | | 7 | John | Taylor | +-----------+------------------+-----------------+ 7 rows in set (0.00 sec)
คำสั่งหาข้อมูลซ้ำ (FirstName + LastName)
mysql> SELECT StudentFirstName, StudentLastName, COUNT(*) AS Total
-> FROM DuplicateDemo
-> GROUP BY StudentFirstName, StudentLastName
-> HAVING COUNT(*) > 1;
+------------------+-----------------+-------+ | StudentFirstName | StudentLastName | Total | +------------------+-----------------+-------+ | John | Smith | 2 | | David | Smith | 2 | +------------------+-----------------+-------+ 2 rows in set (0.00 sec)
การประยุกต์ใช้เพิ่มเติม:
- ลบข้อมูลซ้ำเก็บแถวแรก: ใช้
ROW_NUMBER()(MySQL 8.0+) - หา PK ที่ซ้ำ:
GROUP BY id HAVING COUNT(*) > 1 - สร้าง Unique Index เพื่อป้องกัน:
ALTER TABLE ADD UNIQUE INDEX idx_name (col1, col2)
