MySQL टेबल में डुप्लिकेट रिकॉर्ड कैसे गिनें?

MySQL में डुप्लिकेट रिकॉर्ड गिनने के लिए IF() फ़ंक्शन का उपयोग करें।

सिंटैक्स

SELECT yourColumnName, COUNT(*) AS anyVariableName, 
IF(COUNT(*) > 1, 'Duplicate Records', 'Not Duplicate records') AS anyVariableName 
FROM yourTableName 
GROUP BY yourColumnName;

उदाहरण के साथ समझें

सबसे पहले एक टेबल बनाते हैं:

mysql> CREATE TABLE DuplicateRecords (
    -> Id INT NOT NULL AUTO_INCREMENT,
    -> Name VARCHAR(30),
    -> PRIMARY KEY(Id)
    -> ); 
Query OK, 0 rows affected (0.82 sec)

रिकॉर्ड डालें

mysql> INSERT INTO DuplicateRecords(Name) VALUES('Carol'); 
Query OK, 1 row affected (0.81 sec)
mysql> INSERT INTO DuplicateRecords(Name) VALUES('John'); 
Query OK, 1 row affected (0.17 sec)
mysql> INSERT INTO DuplicateRecords(Name) VALUES('Sam'); 
Query OK, 1 row affected (0.19 sec)
mysql> INSERT INTO DuplicateRecords(Name) VALUES('John'); 
Query OK, 1 row affected (0.17 sec)
mysql> INSERT INTO DuplicateRecords(Name) VALUES('Sam'); 
Query OK, 1 row affected (0.11 sec)
mysql> INSERT INTO DuplicateRecords(Name) VALUES('Sam'); 
Query OK, 1 row affected (0.20 sec)
mysql> INSERT INTO DuplicateRecords(Name) VALUES('John'); 
Query OK, 1 row affected (0.12 sec)
mysql> INSERT INTO DuplicateRecords(Name) VALUES('Carol'); 
Query OK, 1 row affected (0.14 sec)
mysql> INSERT INTO DuplicateRecords(Name) VALUES('Carol'); 
Query OK, 1 row affected (0.10 sec)
mysql> INSERT INTO DuplicateRecords(Name) VALUES('Mike'); 
Query OK, 1 row affected (0.14 sec)

सभी रिकॉर्ड देखें

mysql> SELECT * FROM DuplicateRecords;

आउटपुट

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Carol |
| 2  | John  |
| 3  | Sam   |
| 4  | John  |
| 5  | Sam   |
| 6  | Sam   |
| 7  | John  |
| 8  | Carol |
| 9  | Carol |
| 10 | Mike  |
+----+-------+
10 rows in set (0.00 sec)

डुप्लिकेट रिकॉर्ड गिनने की क्वेरी

mysql> SELECT Name, COUNT(*) AS Repetition, 
    -> IF(COUNT(*) > 1, 'Duplicate Records', 'Not Duplicate records') AS IsDuplicateRecordsOrNot 
    -> FROM DuplicateRecords 
    -> GROUP BY Name;

आउटपुट

+-------+------------+-------------------------+
| Name  | Repetition | IsDuplicateRecordsOrNot |
+-------+------------+-------------------------+
| Carol | 3          | Duplicate Records       |
| John  | 3          | Duplicate Records       |
| Sam   | 3          | Duplicate Records       |
| Mike  | 1          | Not Duplicate records   |
+-------+------------+-------------------------+
4 rows in set (0.00 sec)

GROUP BY के साथ COUNT(*) और IF() का उपयोग करके प्रत्येक नाम की पुनरावृत्ति और डुप्लिकेट स्थिति प्राप्त की गई।