MySQL में GROUP BY HAVING COUNT से अद्वितीय अंतिम नाम वाले उपयोगकर्ता खोजें

सिंटैक्स

select max(tbl.firstName), tbl.lastName, count(distinct tbl.firstName) as CountOfFirstName
from demo76 as tbl
group by tbl.lastName
having count(distinct tbl.firstName) = 1;

उदाहरण डेटा

mysql> create table demo76 (firstName varchar(20), lastName varchar(20));
mysql> insert into demo76 values('John','Doe'), ('David','Smith'), ('Adam','Smith');

परिणाम: केवल अद्वितीय अंतिम नाम (Doe)

+--------------------+----------+------------------+
| max(tbl.firstName) | lastName | CountOfFirstName |
+--------------------+----------+------------------+
| John               | Doe      | 1                |
+--------------------+----------+------------------+

व्याख्या: Smith दो बार आता है (David, Adam), इसलिए HAVING COUNT=1 से बाहर।