LeetCode：数据库技术【180-185】

最新推荐文章于 2024-09-14 19:55:29 发布

weixin_30878501

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量132

点赞数

文章标签：数据库数据结构与算法

原文链接：http://www.cnblogs.com/MrSaver/p/9910742.html

版权

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 
　　Email 
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
                  );