LeetCode Database做题笔记

发表于 更新于 分类于 评论数：
本文字数： 4.9k 阅读时长 ≈ 6 分钟

TODO

  • 不连接交叉连接(即用,在from中分隔两表)和内连接的区别，是不是都可以产生笛卡尔积？
  • 连接时用的on和判断时用的where如何正确使用，同时使用时如何正确使用？详见183方法一
  • 使用group by时，只要是select中非聚集函数的列，都要在 group by当中

NOTE

1. 交叉连接与隐式内连接的区别

个人理解是，没有where就是交叉连接，有where就是隐式内连接。产生的中间表均为笛卡尔积，区别在于隐式内连接会对产生的笛卡尔积通过where进行过滤得到结果。

1
2
SELECT * FROM 表1 CROSS JOIN 表2; --交叉连接
SELECT * FROM 表1，表2

隐式内连接不用on语句，而用where

1
SELECT * FROM 表1,表2 where 表1.关系字段 = 表2.关系字段 --隐式内连接

2. on, where, having的正确使用

此处参考了古月慕南和LYQ1990的博客，个人理解是

  • on用于建立关联关系, where用于筛选关联关系。

  • on是用于生成临时表时使用的条件。在外连接中，不管on的条件是否为真，都会返回left join中的左表信息，或者right join中的右表信息。

  • where是在临时表生成之后对该临时表过滤用的条件，过滤掉条件不为真的所有信息。

  • 在内连接中，条件放在on和where没区别，返回的结果集是相同的。

  • 在优先级方面，on > where > 聚合函数 > having:

    表关联生成临时表， on 条件生效（此时的临时表会因为left join或right join的特性而一定带有主表的记录，也就是主表的记录不会被 on 条件过滤掉） –> 临时表生成完毕，where条件过滤临时表（where条件过滤时因为临时表已经生成完毕，因此不会再具有left join或right join的特性，也就是主表记录也会被where条件过滤掉） –> 临时表过滤完毕，聚合函数进行运算 –> 聚合函数运算完毕，having生效对运算完毕的临时表进行过滤 –>最终的结果表

    原文链接：https://blog.csdn.net/qq_27607579/article/details/83787592

  • having只与group by 连用， 即在分组过后过滤数据，条件中通常包含聚合函数。

  • ① WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。
    ② GROUP BY 子句用来分组 WHERE 子句的输出。
    ③ HAVING 子句用来从分组的结果中筛选行。

181. Employees Earning More Than Their Managers

来源：https://leetcode.com/problems/employees-earning-more-than-their-managers/description/
考察：同一个表内的信息对比，需要把一张表看作两张表分别对比条件进行查询，也就是从这个表里获取两次信息。可以用到内连接，也可以不用内连接直接查询。

Description

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

1
2
3
4
5
6
7
8
+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

1
2
3
4
5
+----------+
| Employee |
+----------+
| Joe      |
+----------+

Solution

此处还要注意题目还要求最终输出结果的表头为Employee

方法一：

1
2
3
4
5
select e1.Name as Employee --更改字段名,as可缺省
from Employee e1, Employee e2
where e1.ManagerId = e2.Id
and e1.Salary > e2.Salary;
--用时461 ms

在where语句之前，可以理解为交叉连接产生笛卡尔积，查询结果有4×4=16个。一般情况下不会使用交叉连接，这这业务需求比较少见。
在有where语句时，语句表示一个隐性内连接，产生的中间表仍为笛卡尔积，结果用where语句过滤掉

方法二：内连接

1
2
3
4
5
select e1.Name as Employee
from Employee e1 inner join Employee e2--inner可缺省
on e1.ManagerId = e2.Id
and e1.Salary > e2.Salary;
--615 ms

方法三：子查询

1
2
3
4
5
6
select e1.Name as Employee
from Employee e1
where Salary > (select Salary
                from Employee e2
                where e2.Id = e1.ManagerId);
--974 ms

183. Customers Who Never Order

来源：https://leetcode.com/problems/customers-who-never-order/description/
考察：查找不存在的数据，连接用is null,子查询用not in

Description

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

1
2
3
4
5
6
7
8
+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Orders 表：

1
2
3
4
5
6
+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

例如给定上述表格，你的查询应返回：

1
2
3
4
5
6
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

Solution

方法一：左外连接


要查询Orders表中不存在，但在Customers表中存在的CustomerId,把Orders放在左连接的右边，就可以查询出Orders中CustomerId为null的数据

1
2
3
4
select c.Name as Customers
from Customers c left join Orders o
on c.Id = o.CustomerId
where o.CustomerId is null;

方法二：子查询

查询不存在的值，可以用where与not in结合的方法，利用子查询

1
2
3
select Name as Customers
from Customers
where Id not in (select CustomerId from Orders)

184. Department Highest Salary

来源：https://leetcode.com/problems/department-highest-salary/description/
考察：使用join和in。in可以用于多个字段,子查询或临时表

Description

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

1
2
3
4
5
6
7
8
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

1
2
3
4
5
6
+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

1
2
3
4
5
6
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

Solution

首先可以在Employee表中根据DepartmentId分组找到每组最高Salary所代表的信息，也就是先创建一个临时表，包含了部门员工的最大薪资：
此处特别注意: select中非聚集函数的列，都要在 group by 中

1
2
3
select DepartmentId, max(Salary)
from Employee
group by DepartmentId

得出结果为

1
2
3
4
| DepartmentId | MAX(Salary) |
|--------------|-------------|
| 1            | 90000       |
| 2            | 80000       |

然后，把表 Employee 和 Department 连接，再在这张临时表里用 IN语句查询部门名字和工资的关系。在in语句中可以同时使用多个字段

1
2
3
4
5
select d.Name Department, e.Name Employee, Salary
from Department d join Employee e on d.Id = e.DepartmentId
where (e.DepartmentId, Salary) in ( select DepartmentId, max(Salary)
                                    from Employee
                                    group by DepartmentId );
0%