MySQL में GROUP BY ... HAVING COUNT(*) > 1 से मल्टी-कॉलम डुप्लीकेट पहचानें
सिंटैक्स:
SELECT yourColumnName1, yourColumnName2, ..., COUNT(*) AS anyVariableName
FROM yourTableName
GROUP BY yourColumnName1, yourColumnName2, ...
HAVING COUNT(*) > 1;
व्यावहारिक उदाहरण
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)
2. डेटा डालें (कुछ FirstName+LastName कॉम्बिनेशन डुप्लीकेट):
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');
3. सभी रिकॉर्ड:
+-----------+------------------+-----------------+
| 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)
4. डुप्लीकेट खोजें (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)
मुख्य बिंदु:
GROUP BY में वे सभी कॉलम शामिल करें जिनके कॉम्बिनेशन पर डुप्लीकेट जांचना हैHAVING COUNT(*) > 1 केवल उन्हीं समूहों को रखता है जो एक से अधिक बार आते हैंCOUNT(*) पंक्तियों की गिनती करता है (NULL सहित)
उन्नत: डुप्लीकेट रो की पूरी डिटेल देखें:
SELECT d.*
FROM DuplicateDemo d
JOIN (
SELECT StudentFirstName, StudentLastName
FROM DuplicateDemo
GROUP BY StudentFirstName, StudentLastName
HAVING COUNT(*) > 1
) dup ON d.StudentFirstName = dup.StudentFirstName AND d.StudentLastName = dup.StudentLastName
ORDER BY d.StudentFirstName, d.StudentLastName;
डुप्लीकेट हटाना (एक रखकर):
-- MySQL 8.0+ CTE के साथ
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY StudentFirstName, StudentLastName ORDER BY StudentId) as rn
FROM DuplicateDemo
)
DELETE FROM DuplicateDemo WHERE StudentId IN (
SELECT StudentId FROM ranked WHERE rn > 1
);
