mysql - 查找具有唯一姓氏的所有用户

Question

我有一张桌子users。

mysql> SELECT * FROM `actors`;
+-----------+------------+------------+------------+
| actors_id | first_name | last_name  | dob        |
+-----------+------------+------------+------------+
|         1 | name1      | lastname1  | 1989-06-01 |
|         2 | name2      | lastname2  | 1989-05-02 |
|         3 | name3      | lastname2  | 1989-06-03 |
+-----------+------------+------------+------------+

我写了一个 sql 查询，显示所有具有唯一姓氏的用户

SELECT MAX(a.first_name), a.last_name
FROM actors AS a
GROUP BY a.last_name
HAVING COUNT(DISTINCT a.first_name) = 1;

告诉我为什么查询会删除折叠的用户 name2 和 name3？ HAVING COUNT(DISTINCT u.first_name) = 1;

这个怎么运作？帮助理解它是如何工作的

标签： mysqlsqldatabase

Answer 1

当您运行此查询时

SELECT MAX(a.first_name), a.last_name,COUNT(DISTINCT a.first_name)
FROM actors AS a
GROUP BY a.last_name

result:
+-------------------------+------------+----------------------------------+
| MAX(a.first_name)       | last_name  |    COUNT(DISTINCT a.first_name)  |
+-------------------------+------------+----------------------------------+
| name1                   | lastname1  |             1                    |
| name2                   | lastname2  |             2                    |
+-------------------------+------------+----------------------------------+


Now 

HAVING COUNT(DISTINCT u.first_name) = 1;

then:


SELECT MAX(a.first_name), a.last_name,COUNT(DISTINCT a.first_name)
FROM actors AS a
GROUP BY a.last_name
HAVING COUNT(DISTINCT a.first_name) = 1;


 +-------------------------+------------+---------------------------------+
| MAX(a.first_name)       | last_name  |    COUNT(DISTINCT a.first_name)  |
+-------------------------+------------+----------------------------------+
| name1                   | lastname1  |             1                    |
+-------------------------+------------+----------------------------------+