LeetCode刷题笔记 - 182. Duplicate Emails

2018-10-22

182.Duplicate Emails

一、Description：

Write a SQL query to find all duplicate emails in a table named Person.

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

For example, your query should return the following for the above table:

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

Note: All emails are in lowercase.

二、Solution:

解法一：

#Approach I: Using GROUP BY and a temporary table [Accepted]
select Email from
(
  select Email, count(Email) as num
  from Person
  group by Email
) as statistic
where num > 1;

解法二：

#Approach II: Using GROUP BY and HAVING condition将条件添加到GROUP BY的一种更常用的方法是使用HAVING子句，它要简单得多，效率也更高。
select Email
from Person
group by Email
having count(Email) > 1;

三、总结知识点:

1、过滤数据：Where子句

数据库表一般包含大量的数据，很少需要检索表中的所有行。通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指定 搜索条件（ search criteria ），搜索条件也称为 过滤条件（ filter condition ）。
在 SELECT 语句中，数据根据 WHERE 子句中指定的搜索条件进行过滤。 WHERE 子句在表名（ FROM 子句）之后给出。

提示： SQL 过滤与应用过滤
数据也可以在应用层过滤。为此， SQL 的 SELECT 语句为客户端应用检索出超过实际所需的数据，然后客户端代码对返回数据进行循环，提取出需要的行。
通常，这种做法极其不妥。优化数据库后可以更快速有效地对数据进行过滤。而让客户端应用（或开发语言）处理数据库的工作将会极大地影响应用的性能，并且使所创建的应用完全不具备可伸缩性。此外，如果在客户端过滤数据，服务器不得不通过网络发送多余的数据，这将导致网络带宽的浪费。

（1）、WHERE子句操作符

SQL 支持下表列出的所有条件WHERE 子句操作符。

操作符	说明
=	等于
< >	不等于
!=	不等于
<	小于
<=	小于等于
!	不小于
>	大于
>=	大于等于
!>	不大于
BETWEEN	在指定的两个值之间
IS NULL	为 NULL 值

警告： 操作符兼容
表 4-1 中列出的某些操作符是冗余的（如 < > 与 != 相同， !< 相当于 >= ）。并非所有 DBMS 都支持这些操作符。想确定你的 DBMS 支持哪些操作符，请参阅相应的文档。

a、检查单个值

=、<、>、<=等

b、 不匹配检查

<>、!< 等

?提示：何时使用引号
如果仔细观察 WHERE 子句中的条件，有的值括在单引号内，而有的值未括起来。单引号用来限定字符串。如果将值与字符串类型的列进行比较，就需要限定引号。用来与数值列进行比较的值不用引号。

警告：是 != 还是 <> ？
!= 和 <> 通常可以互换。但是，并非所有 DBMS 都支持这两种不等于操作符。例如， Microsoft Access 支持 <> 而不支持 != 。如果有疑问，请参阅相应的 DBMS 文档。

c、 范围值检查

要检查某个范围的值，可以使用 BETWEEN 操作符。其语法与其他 WHERE 子句的操作符稍有不同，因为它需要两个值，即范围的开始值和结束值。
例如：

输入 ▼
SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;

输出 ▼
prod_name prod_price
------------------- ----------
8 inch teddy bear 5.99
12 inch teddy bear 8.99
King doll 9.49
Queen doll 9.49

从这个例子可以看到，在使用 BETWEEN 时，必须指定两个值 —— 所需范围的低端值和高端值。这两个值必须用 AND 关键字分隔。 BETWEEN 匹配范围中所有的值，包括指定的开始值和结束值。

d、 空值检查

在创建表时，表设计人员可以指定其中的列能否不包含值。在一个列不包含值时，称其包含空值 NULL 。

NULL
无值（ no value ），它与字段包含 0 、空字符串或仅仅包含空格不同。

确定值是否为 NULL ，不能简单地检查是否 = NULL 。 SELECT 语句有一个特殊的 WHERE 子句，可用来检查具有 NULL 值的列。这个 WHERE 子句就是 IS NULL 子句。其语法如下：

输入 ▼
SELECT prod_name
FROM Products
WHERE prod_price IS NULL;

这条语句返回所有没有价格（空 prod_price 字段，不是价格为 0 ）的产品。

提示：各 DBMS 特有的操作符
许多 DBMS 扩展了标准的操作符集，提供了更高级的过滤选择。更多信息请参阅相应的 DBMS 文档。

?警告： NULL 和非匹配
通过过滤选择不包含指定值的所有行时，你可能希望返回含 NULL 值的行。但是这做不到。因为未知（ unknown ）有特殊的含义，数据库不知道它们是否匹配，所以在进行匹配过滤或非匹配过滤时，不会返回这些结果。
过滤数据时，一定要验证被过滤列中含 NULL 的行确实出现在返回的数据中。

（2）、高级数据过滤 - 组合 WHERE 子句

上面介绍的所有 WHERE 子句在过滤数据时使用的都是单一的条件。为了进行更强的过滤控制， SQL 允许给出多个 WHERE 子句。这些子句有两种使用方式，即以 AND 子句或 OR 子句的方式使用。

操作符（ operator ）
用来联结或改变 WHERE 子句中的子句的关键字，也称为 逻辑操作符（ logical operator ）。

a、 AND 操作符

要通过不止一个列进行过滤，可以使用 AND 操作符给 WHERE 子句附加条件。

输入 ▼
SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

这条 SELECT 语句中的 WHERE 子句包含两个条件，用 AND 关键字联结在一起。 AND 指示 DBMS 只返回满足所有给定条件的行。

AND
用在 WHERE 子句中的关键字，用来指示检索满足所有给定条件的行。可以增加多个过滤条件，每个条件间都要使用 AND 关键字。

b、 OR操作符

OR 操作符与 AND 操作符正好相反，它指示 DBMS 检索匹配任一条件的行。事实上，许多 DBMS 在 OR WHERE 子句的第一个条件得到满足的情况下，就不再计算第二个条件了（在第一个条件满足时，不管第二个条件是否满足，相应的行都将被检索出来）。

输入 ▼
SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’;

OR
WHERE 子句中使用的关键字，用来表示检索匹配任一给定条件的行。

c、 求值顺序

WHERE 子句可以包含任意数目的 AND 和 OR 操作符。允许两者结合以进行复杂、高级的过滤。
但是，SQL （像多数语言一样）在处理 OR 操作符前，优先处理 AND 操作符。需要使用圆括号对操作符进行明确分组，因为圆括号具有比 AND 或 OR 操作符更高的求值顺序。

?提示：在 WHERE 子句中使用圆括号
任何时候使用具有 AND 和 OR 操作符的 WHERE 子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认求值顺序，即使它确实如你希望的那样。使用圆括号没有什么坏处，它能消除歧义。

（3）、高级数据过滤 - IN 操作符

IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配。 IN 取一组由逗号分隔、括在圆括号中的合法值。
IN 操作符后跟由逗号分隔的合法值，这些值必须括在圆括号中。
IN 操作符 = OR 操作符
为什么要使用 IN 操作符？其优点为：

• 在有很多合法选项时， IN 操作符的语法更清楚，更直观。
• 在与其他 AND 和 OR 操作符组合使用 IN 时，求值顺序更容易管理。
• IN 操作符一般比一组 OR 操作符执行得更快。
• IN 的最大优点是可以包含其他 SELECT 语句，能够更动态地建立 WHERE 子句。

IN
WHERE 子句中用来指定要匹配值的清单的关键字，功能与 OR 相当。

（4）、高级数据过滤 - NOT 操作符

WHERE 子句中的 NOT 操作符有且只有一个功能，那就是否定其后所跟的任何条件。因为NOT 从不单独使用（它总是与其他操作符一起使用），所以它的语法与其他操作符有所不同。 NOT 关键字可以用在要过滤的列前，而不仅是在其后。

NOT
WHERE 子句中用来否定其后条件的关键字。
例子：

SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

这里的 NOT 否定跟在其后的条件，因此， DBMS 不是匹配 vend_id 为 DLL01 ，而是匹配非 DLL01 之外的所有东西。
上面的例子也可以使用 <> 操作符来完成。
为什么使用 NOT ？对于这里的这种简单的 WHERE 子句，使用 NOT 确实没有什么优势。但在更复杂的子句中， NOT 是非常有用的。例如，在与 IN 操作符联合使用时， NOT 可以非常简单地找出与条件列表不匹配的行。

说明： MariaDB 中的 NOT
MariaDB 支持使用 NOT 否定 IN 、 BETWEEN 和 EXISTS 子句。大多数 DBMS 允许使用 NOT 否定任何条件

2、用通配符进行过滤 - LIKE操作符

前面介绍的所有操作符都是针对已知值进行过滤的。不管是匹配一个值还是多个值，检验大于还是小于已知值，或者检查某个范围的值，其共同点是过滤中使用的值都是已知的。
但是，这种过滤方法并不是任何时候都好用。例如，怎样搜索产品名中包含文本 bean bag 的所有产品？用简单的比较操作符肯定不行，必须使用通配符。利用通配符，可以创建比较特定数据的搜索模式。

通配符（ wildcard ）
用来匹配值的一部分的特殊字符。
搜索模式（ search pattern ）
由字面值、通配符或两者组合构成的搜索条件。

通配符本身实际上是 SQL 的 WHERE 子句中有特殊含义的字符， SQL 支持几种通配符。为在搜索子句中使用通配符，必须使用 LIKE 操作符。 LIKE 指示 DBMS ，后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。

谓词（ predicate ）
操作符何时不是操作符？答案是，它作为谓词时。从技术上说， LIKE 是谓词而不是操作符。虽然最终的结果是相同的，但应该对此术语有所了解，以免在 SQL 文献或手册中遇到此术语时不知所云。

通配符搜索只能用于文本字段（串），非文本数据类型字段不能使用通配符搜索。

（1）、百分号（ % ）通配符

最常使用的通配符是百分号（ % ）。在搜索串中， % 表示任何字符出现任意次数。
例如，为了找出所有以词 Fish 起头的产品，可发布以下 SELECT 语句：

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';

此例子使用了搜索模式 ‘Fish%’ 。在执行这条子句时，将检索任意以 Fish 起头的词。 % 告诉 DBMS 接受 Fish 之后的任意字符，不管它有多少字符。

说明： Access 通配符
如果使用的是 Microsoft Access ，需要使用 * 而不是 % 。
说明：区分大小写
根据 DBMS 的不同及其配置，搜索可以是区分大小写的。如果区分大小写，则 ‘fish%’ 与 Fish bean bag toy 就不匹配。

通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符。下面的例子使用两个通配符，它们位于模式的两端：

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';

搜索模式 ‘%bean bag%’ 表示匹配任何位置上包含文本 bean bag 的值，不论它之前或之后出现什么字符。
通配符也可以出现在搜索模式的中间，虽然这样做不太有用。下面的例子找出以 F 起头、以 y 结尾的所有产品：

SELECT prod_name
FROM Products
WHERE prod_name LIKE 'F%y';

需要特别注意，除了能匹配一个或多个字符外， % 还能匹配 0 个字符。 % 代表搜索模式中给定位置的 0 个、 1 个或多个字符。

说明：请注意后面所跟的空格
包括 Access 在内的许多 DBMS 都用空格来填补字段的内容。例如，如果某列有 50 个字符，而存储的文本为 Fish bean bag toy （ 17 个字符），则为填满该列需要在文本后附加 33 个空格。这样做一般对数据及其使用没有影响，但是可能对上述 SQL 语句有负面影响。子句 WHERE prod_name LIKE ‘F%y’ 只匹配以 F 开头、以 y 结尾的 prod_name 。如果值后面跟空格，则不是以 y 结尾，所以 Fish bean bag toy 就不会检索出来。简单的解决办法是给搜索模式再增加一个 % 号： ‘F%y%’ 还匹配 y 之后的字符（或空格）。更好的解决办法是用函数去掉空格。使用 SQL 的 TRIM()（ RTRIM() （正如刚才所见，它去掉字符串右边的空格）、 LTRIM() （去掉字符串左边的空格）以及 TRIM() （去掉字符串左右两边的空格） 函数来完成。

警告：请注意 NULL
通配符 % 看起来像是可以匹配任何东西，但有个例外，这就是 NULL 。子句 WHERE prod_name LIKE ‘%’ 不会匹配产品名称为 NULL 的行。

（2）、下划线（ _ ）通配符

另一个有用的通配符是下划线（ _ ）。下划线的用途与 % 一样，但它只匹配单个字符，而不是多个字符。

说明： DB2 通配符
DB2 不支持通配符 _ 。
说明： Access 通配符
如果使用的是 Microsoft Access ，需要使用 ? 而不是 _ 。

与 % 能匹配 0 个字符不同， _ 总是刚好匹配一个字符，不能多也不能少。

（3）、方括号（ [ ] ）通配符

方括号（ [] ）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。

说明：并不总是支持集合
与前面描述的通配符不一样，并不是所有 DBMS 都支持用来创建集合的 [] 。只有微软的 Access 和 SQL Server 支持集合。为确定你使用的DBMS 是否支持集合，请参阅相应的文档。

例如，找出所有名字以 J 或 M 起头的联系人，可进行如下查询：

输入 ▼
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

输出 ▼
cust_contact
-----------------
Jim Jones
John Smith
Michelle Green

此语句的 WHERE 子句中的模式为 ‘[JM]%’ 。这一搜索模式使用了两个不同的通配符。 [JM] 匹配任何以方括号中字母开头的联系人名，它也只能匹配单个字符。因此，任何多于一个字符的名字都不匹配。 [JM] 之后的 % 通配符匹配第一个字符之后的任意数目的字符，返回所需结果。

此通配符可以用前缀字符 ^ （脱字号）来否定。例如，下面的查询匹配不以 J 或 M 起头的任意联系人名（与前一个例子相反）：

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%'
ORDER BY cust_contact;

说明： Access 中的否定集合
如果使用的是 Microsoft Access ，需要用 ! 而不是 ^ 来否定一个集合，因此，使用的是 [!JM] 而不是 [^JM] 。

当然，也可以使用 NOT 操作符得出相同的结果。 ^ 的唯一优点是在使用多个 WHERE 子句时可以简化语法：

SELECT cust_contact
FROM Customers
WHERE NOT cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

（4）、 使用通配符的技巧

SQL 的通配符很有用。但这种功能是有代价的，即通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。这里给出一些使用通配符时要记住的技巧。

• 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
• 在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。
• 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

总之，通配符是一种极其重要和有用的搜索工具，以后我们经常会用到它。

3、Group By子句和Having子句：

不使用Group By子句和Having子句，所有计算（sum,count,max,average等）都是在表的所有数据或匹配特定的 WHERE 子句的数据上进行的

（1）、数据分组

使用Group By子句和Having子句，分组可以将数据分为多个逻辑组，对每个组进行聚集计算。

（2）、创建分组：Group By子句

因为使用了 GROUP BY ，就不必指定要计算和估值的每个组了。系统会自动完成。 GROUP BY 子句指示 DBMS 分组数据，然后对每个组而不是整个结果集进行聚集。

在使用 GROUP BY 子句前，需要知道一些重要的规定。

• GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
• 如果在 GROUP BY 子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
• GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在 SELECT 中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式。不能使用别名。
• 大多数 SQL 实现不允许 GROUP BY 列带有长度可变的数据类型（如文本或备注型字段）。
• 除聚集计算语句外， SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。
• 如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。
• GROUP BY 子句必须出现在 WHERE 子句之后， ORDER BY 子句之前。

?提示： ALL 子句
Microsoft SQL Server 等有些 SQL 实现在 GROUP BY 中支持可选的 ALL 子句。这个子句可用来返回所有分组，即使是没有匹配行的分组也返回（在此情况下，聚集将返回 NULL ）。具体的 DBMS 是否支持 ALL ，请参阅相应的文档。

?警告：通过相对位置指定列
有的 SQL 实现允许根据 SELECT 列表中的位置指定 GROUP BY 的列。例如， GROUP BY 2, 1 可表示按选择的第二个列分组，然后再按第一个列分组。虽然这种速记语法很方便，但并非所有 SQL 实现都支持，并且使用它容易在编辑 SQL 语句时出错。

（3）、过滤分组：Having子句

除了能用 GROUP BY 分组数据外， SQL 还允许过滤分组，规定包括哪些分组，排除哪些分组。例如，你可能想要列出至少有两个订单的所有顾客。为此，必须基于完整的分组而不是个别的行进行过滤。

但是，在这个例子中 WHERE 不能完成任务，因为WHERE 过滤指定的是行而不是分组。事实上， WHERE 没有分组的概念。

那么，不使用 WHERE 使用什么呢？ SQL 为此提供了另一个子句，就是 HAVING 子句。 HAVING 非常类似于 WHERE 。事实上，目前为止所学过的所有类型的 WHERE 子句都可以用 HAVING 来替代。唯一的差别是， WHERE 过滤行，而 HAVING 过滤分组。

提示： HAVING 支持所有 WHERE 操作符
WHERE 子句的条件（包括通配符条件和带多个操作符的子句）。这些有关 WHERE 的所有技术和选项都适用于 HAVING 。它们的句法是相同的，只是关键字有差别。
例子：

输入 ▼
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

输出 ▼
cust_id orders
---------- -----------
1000000001 2

这条 SELECT 语句的最后一行增加了 HAVING 子句，它过滤 COUNT(*) >= 2 （两个以上订单）的那些分组。

可以看到， WHERE 子句在这里不起作用，因为过滤是基于分组聚集值，而不是特定行的值。

?说明： HAVING 和 WHERE 的差别
这里有另一种理解方法，WHERE 在数据分组前进行过滤， HAVING 在数据分组后进行过滤。这是一个重要的区别， WHERE 排除的行不包括在分组中。这可能会改变计算值，从而影响 HAVING 子句中基于这些值过滤掉的分组。

那么，有没有在一条语句中同时使用 WHERE 和 HAVING 子句的需要呢？事实上，确实有。假如想进一步过滤上面的语句，使它返回过去 12 个月内具有两个以上订单的顾客。为此，可增加一条 WHERE 子句，过滤出过去 12 个月内下过的订单，然后再增加 HAVING 子句过滤出具有两个以上订单的分组。

?说明：使用 HAVING 和 WHERE
HAVING 与 WHERE 非常类似，如果不指定 GROUP BY ，则大多数 DBMS 会同等对待它们。不过，你自己要能区分这一点。使用 HAVING 时应该结合 GROUP BY 子句，而 WHERE 子句用于标准的行级过滤。

（4）、分组和排序

GROUP BY 和 ORDER BY 经常完成相同的工作，但它们非常不同，理解这一点很重要。下表汇总了它们之间的差别。

ORDER BY	GROUP BY
对产生的输出排序	对行分组，但输出可能不是分组的顺序
任意列都可以使用（甚至非选择的列也可以使用）	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

表中列出的第一项差别极为重要。我们经常发现，用 GROUP BY 分组的数据确实是以分组顺序输出的。但并不总是这样，这不是 SQL 规范所要求的。此外，即使特定的 DBMS 总是按给出的 GROUP BY 子句排序数据，用户也可能会要求以不同的顺序排序。就因为你以某种方式分组数据（获得特定的分组聚集值），并不表示你需要以相同的方式排序输出。应该提供明确的 ORDER BY 子句，即使其效果等同于 GROUP BY 子句。

?提示：不要忘记 ORDER BY
一般在使用 GROUP BY 子句时，应该也给出 ORDER BY 子句。这是保证数据正确排序的唯一方法。千万不要仅依赖 GROUP BY 排序数据。

4、Select子句顺序：

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否