贴答案
SELECT Email FROM Person GROUP BY Email HAVING COUNT(*)>1;
不得不说没有实战经验对很多东西是没印象的。
leetcode上被标记为easy的题目我也花了很久。
还是对sql的语法结果没有了解。
比如GROUP BY属于clause
HAVING也是clause
念书时候就知道的WHERE也是clause
但是COUNT不是clause
那HAVING和WHERE是什么区别?
oracle上的文档写得非常清楚:
A HAVING clause restricts the results of a GROUP BY in a SelectExpression. The HAVING clause is applied to each group of the grouped table, much as a WHERE clause is applied to a select list. If there is no GROUP BY clause, the HAVING clause is applied to the entire result as a single group. The SELECT clause cannot refer directly to any column that does not have a GROUP BY clause. It can, however, refer to constants, aggregates, and special registers.
HAVING 是用来限制GROUP BY子句的结构的。
HAVING作用于GROUP BY 之后的每一个group。
可见group之后,我们就应该在脑子里出现一个grouped table。
就像WHERE应用在list一样。
如果没有GROUP BY,那么 HAVING会把整个结果当作一个单独的group。
但是如果没有 GROUP BY,HAVING 里如果使用COUNT(*) 计算,那么COUNT计算的是什么?
我们发现COUNT和COUNT(*) 是两个不一样的函数:
COUNT(*) is an aggregate function that counts the number of rows accessed. No NULLs or duplicates are eliminated. COUNT(*) does not operate on an expression.
这玩意是用来计算访问的列的数目的。
所以类似这样:
SELECT COUNT(*) FROM person;
一般就一行,而且显示的就是总共的列数。
因为COUNT的结果只有一行,所以别的列有多行也只显示一行。
更多还是看文档吧!