LeetCode：数据库技术【180-185】
180.连续出现的数字
题目描述
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
+----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。
+-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+
题目分析
这道题目已经不是基础题目了，我们需要转换思维了。题目中明确说了是连续出现了三次相同值的值，我们在单张表中已经无法掌握了，只能使用自联结来处理。
SELECT
DISTINCT l1.Num AS ConsecutiveNums
FROM
Logs l1,Logs l2,Logs l3
WHERE
l2.Id = l1.Id+1
AND l3.Id = l2.Id+1
AND l1.Num =L2.Num
AND l2.Num =l3.NUm;
关于自联结与子查询：自联结通常作为外部语句用来替代 从相同表中检索数据时使用的子查询语句。虽然最终的结果是 相同的，但有时候处理联结远比处理子查询快得多。应该试一 下两种方法，以确定哪一种的性能更好
181.超过经理收入的员工
题目描述
Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。
+----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL | | 4 | Max | 90000 | NULL | +----+-------+--------+-----------+
给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。
+----------+ | Employee | +----------+ | Joe | +----------+
题目分析
这道题目是数据库的基础题型，可以用自联结来做，自联结的话，首先是一条SELECT语句中两次引用相同的表，然后WHERE语句中指明条件即可。
SELECT
e1.Name AS Employee
FROM
Employee e1,Employee e2
WHERE
e1.ManagerId = e2.Id
AND e1.Salary>e2.Salary;
182.查找重复的电子邮箱
题目描述
编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。
示例：
+----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+
根据以上输入，你的查询应返回以下结果：
+---------+ | Email | +---------+ | a@b.com | +---------+
说明：所有电子邮箱都是小写字母。
题目分析
SELECT
FROM
Person
GROUP BY Email
HAVING COUNT(*)>1;
除了能用GROUP BY分组数据外，MySQL还允许过滤分组，规定包括 哪些分组，排除哪些分组。例如，可能想要列出至少有两个订单的所有 顾客。为得出这种数据，必须基于完整的分组而不是个别的行进行过滤。
我们已经看到了WHERE子句的作用。但是，在这个例 子中WHERE不能完成任务，因为WHERE过滤指定的是行而不是分组。事实 上，WHERE没有分组的概念。
HAVING和WHERE的差别 ：
这里有另一种理解方法，WHERE在数据 分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重 要的区别，WHERE排除的行不包括在分组中。这可能会改变计 算值，从而影响HAVING子句中基于这些值过滤掉的分组。
183.从不订购的客户
题目描述
某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
Customers 表：
+----+-------+ | Id | Name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+
Orders 表：
+----+------------+ | Id | CustomerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+
例如给定上述表格，你的查询应返回：
+-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+
题目分析
IN 运算符用于 WHERE 表达式中，以列表项的形式支持多个选择，语法如下：
WHERE column IN (value1,value2,...) WHERE column NOT IN (value1,value2,...)
这里很明显，我们是采用NOT IN，同样也是基础题型：
SELECT
Name AS Customers
FROM
Customers
WHERE
Id NOT IN(SELECT CustomerId FROM Orders);
184.部门工资最高的员工
题目描述
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | +----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | Sales | Henry | 80000 | +------------+----------+--------+
题目分析
这道题有很多知识点值得我们分析，利用Employee表我们可以分析出工资最高的员工的工资和所在部门：这是这道题的切入点，分析出员工的姓名是没意义的，同个部门的最高工资可能不止一个人。
SELECT
DepartmentId,MAX(Salary)
FROM
Employee
GROUP BY
DepartmentId
因为这两个信息横跨了两张表，所以我们可以据此出发，先让两张表做笛卡尔积，然后选出符合上述条件的记录，再按照格式输出即可。
代码如下：
SELECT
Department.Name AS Department,
Employee.Name AS Employee,
Salary
FROM
Employee,Department
WHERE
Employee.DepartmentId = Department.Id
AND
(Employee.DepartmentId,Salary)
IN
(SELECT
DepartmentId,MAX(Salary)
FROM
Employee
GROUP BY
DepartmentId
);
185.部门工资前三高的员工
题目分析
题目内容同上，知识要找到前三高的员工。这道题我还暂时没有思路，参考的官方代码如下：
select d.Name Department, e1.Name Employee, e1.Salary
from Employee e1
join Department d
on e1.DepartmentId = d.Id
where 3 > (select count(distinct(e2.Salary))
from Employee e2
where e2.Salary > e1.Salary
and e1.DepartmentId = e2.DepartmentId
);
总结
180-185是六道题，其中除了181，182其他的题目都有一定难度，我们现在简单总结一下思路：
- 自联结可以处理连续出现的问题，连续出现几次，就自联结几次，只要在WHERE条件中指明他们ID的递增关系即可。
- 使用INNOT IN来限定范围取值。
- 注意HAVING与WHERE的区别，一个是分组的筛选，一个是行的筛选。
- 如果结果涉及多张表的字段，并且存在较复杂的关联关系，我们可以从某种表中找出多张表的共有项，然后笛卡尔积这几张表，筛选中符合条件的记录。