leetcode 难度【简单模式】标签【数据库】题型整理大全

175. 组合两个表

表: Person

+-------------+---------+
| 列名         | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+

personId 是该表的主键（具有唯一值的列）。该表包含一些人的 ID 和他们的姓和名的信息。

表: Address

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+

addressId 是该表的主键（具有唯一值的列）。该表的每一行都包含一个 ID = PersonId 的人的城市和州的信息。

**编写解决方案，报告 Person 表中每个人的姓、名、城市和州。如果 personId 的地址不在 Address 表中，则报告为 null 。**以 任意顺序 返回结果表。结果格式如下所示。

示例 1:输入: Person表:

+----------+----------+-----------+
| personId | lastName | firstName |
+----------+----------+-----------+
| 1        | Wang     | Allen     |
| 2        | Alice    | Bob       |
+----------+----------+-----------+

Address表:

+-----------+----------+---------------+------------+
| addressId | personId | city          | state      |
+-----------+----------+---------------+------------+
| 1         | 2        | New York City | New York   |
| 2         | 3        | Leetcode      | California |
+-----------+----------+---------------+------------+

输出:

+-----------+----------+---------------+----------+
| firstName | lastName | city          | state    |
+-----------+----------+---------------+----------+
| Allen     | Wang     | Null          | Null     |
| Bob       | Alice    | New York City | New York |
+-----------+----------+---------------+----------+

解释: 地址表中没有 personId = 1 的地址，所以它们的城市和州返回 null。
addressId = 1 包含了 personId = 2 的地址信息。

解答如下

select firstName, lastName,city, state from Person left join Address onPerson.personId = Address.personId;

主要考察的是left join的使用

181. 超过经理收入的员工

表：Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| salary      | int     |
| managerId   | int     |
+-------------+---------+

id 是该表的主键（具有唯一值的列）。该表的每一行都表示雇员的ID、姓名、工资和经理的ID。编写解决方案，找出收入比经理高的员工。以 任意顺序 返回结果表。结果格式如下所示。

示例 1:输入: Employee 表:

+----+-------+--------+-----------+
| id | name  | salary | managerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | Null      |
| 4  | Max   | 90000  | Null      |
+----+-------+--------+-----------+

输出:

+----------+
| Employee |
+----------+
| Joe      |
+----------+

解释: Joe 是唯一挣得比经理多的雇员。

解答如下

select e1.name as Employee from Employee e1,Employee e2 where e1.ManagerId = e2.idand e1.Salary > e2.Salary;

考察笛卡尔积的用法

SELECT * FROM Employee e1, Employee e2;

生成的结果是 笛卡尔积（也叫 交叉连接 或 Cartesian Join）。这是两张表之间的所有组合行的集合。

182. 查找重复的电子邮箱

表: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+

id 是该表的主键（具有唯一值的列）。此表的每一行都包含一封电子邮件。电子邮件不包含大写字母。
编写解决方案来报告所有重复的电子邮件。 请注意，可以保证电子邮件字段不为 NULL。以 任意顺序 返回结果表。

结果格式如下例。

示例 1:输入: Person 表:

+----+---------+
| id | email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

输出:

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

解释: a@b.com 出现了两次。

解答如下

select email as Email from Person group by email having count(*) > 1;

考察对于count(*)和having的用法

COUNT(*)

COUNT(*) 会统计表中符合查询条件的所有行的数量，包括 NULL 值的行。也就是说，它会对查询结果中的每一行进行计数，而不管这一行的具体列值是什么。

COUNT(*) 与 COUNT(column) 的区别

COUNT(*)：计算所有行数，不论列值是否为 NULL。
COUNT(column)：只计算某一列中非 NULL 值的行数。NULL 值不会被计算在内。

where和vaing之间的区别用法

1. WHERE 与 HAVING 的区别

• WHERE：用于过滤原始数据行，在 GROUP BY 之前执行，也就是说，WHERE 子句无法使用聚合函数（如 COUNT()、SUM() 等）。它只能应用在数据的原始列上，不能用于过滤基于分组后的结果。

• HAVING：font color=“red”>用于过滤 GROUP BY 分组后的结果，允许你基于聚合函数的结果进行筛选。HAVING 在 GROUP BY 之后执行，因此你可以使用 COUNT() 这样的聚合函数。

183.从不订购的客户

Customers 表：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+

在 SQL 中，id 是该表的主键。该表的每一行都表示客户的 ID 和名称。
Orders 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| customerId  | int  |
+-------------+------+

在 SQL 中，id 是该表的主键。customerId 是 Customers 表中 ID 的外键( Pandas 中的连接键)。该表的每一行都表示订单的 ID 和订购该订单的客户的 ID。找出所有从不点任何东西的顾客。以 任意顺序 返回结果表。结果格式如下所示。

示例 1：输入：Customers table:

+----+-------+
| id | name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Orders table:

+----+------------+
| id | customerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

输出：

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

解答如下

select name as Customers from customers left join Orders on customers.id = Orders.customerid where Orders.customerid is NULL;

本题考察 对于空值的用法
在 MySQL 中，要筛选某一列为空 (NULL) 的值，可以使用 IS NULL 来进行筛选。

NULL 与空字符串或零不同：NULL 表示没有值，而空字符串 (‘’) 或零 (0) 是有效的值，IS NULL 只会筛选出那些确实没有值的记录。
IS NULL 与 = 的区别：在 MySQL 中，NULL 是特殊的，不能直接用 = 比较。因此，不能使用 WHERE column_name = NULL，而是要使用 WHERE column_name IS NULL。

196.删除重复的电子邮箱

表: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+

id 是该表的主键列(具有唯一值的列)。该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。
编写解决方案 删除 所有重复的电子邮件，只保留一个具有最小 id 的唯一电子邮件。
对于 SQL 用户，请注意你应该编写一个 DELETE 语句而不是 SELECT 语句。）
对于 Pandas 用户，请注意你应该直接修改 Person 表。）
运行脚本后，显示的答案是 Person 表。驱动程序将首先编译并运行您的代码片段，然后再显示 Person 表。Person 表的最终顺序 无关紧要 。
返回结果格式如下示例所示。

示例 1:输入: Person 表:

+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+

输出:

+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

解释: john@example.com重复两次。我们保留最小的Id = 1。

解答如下

delete from Person where id not in (select * from (select min(id) from Person GROUP BY email) t);

考察在delete中进行查询，以及派生表的用法

在 DELETE 或 UPDATE 操作中，你不能同时对一个表进行修改并在子查询中查询该表.换句话说，你不能在 DELETE 语句中修改 Person 表，同时在 FROM 子查询中查询 Person 表。

我们需要用一个额外的嵌套子查询（也就是派生表）来规避这个限制，t 是派生表（子查询结果）的别名，虽然这个别名在查询中没有进一步的使用，但它是 MySQL 的一个要求，每个子查询都必须有别名。通过使用派生表，可以避免 MySQL 中同时修改和查询同一个表时产生的错误。

197.上升的温度

表： Weather

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+

id 是该表具有唯一值的列。没有具有相同 recordDate 的不同行。该表包含特定日期的温度信息
编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的 id 。返回结果 无顺序要求 。
结果格式如下例子所示。
示例 1：输入：Weather 表：

+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+

输出：

+----+
| id |
+----+
| 2  |
| 4  |
+----+

解释：
2015-01-02 的温度比前一天高（10 -> 25）
2015-01-04 的温度比前一天高（20 -> 30）

结果输出

select w1.id from weather w1,weather w2 where DATEDIFF(w1.recordDate,w2.recordDate) = 1 and w1.temperature > w2.temperature;

考察笛卡尔积和DATADIFF函数用法

DATEDIFF() 是 MySQL 中的一个函数，用于计算两个日期之间的差值，以天数表示。

语法

DATEDIFF(date1, date2)

• date1 和 date2 是两个日期表达式。DATEDIFF 函数会计算 date1 - date2，并返回相差的天数。
• 结果为整数，正数表示 date1 晚于 date2，负数表示 date1 早于 date2。

示例
1.计算两个具体日期之间的差值：

SELECT DATEDIFF('2024-09-17', '2024-09-10') AS days_diff;

输出结果为

days_diff
7

2.计算某个日期与当前日期之间的差值：

SELECT DATEDIFF(CURDATE(), '2023-01-01') AS days_diff;

假设当前日期为 2024-09-17，则输出结果为：

days_diff
625

3.在表中使用 DATEDIFF 进行筛选： 假设有一个表 orders，其中有一列 order_date，你可以查询所有距离当前日期超过 30 天的订单：

SELECT * 
FROM orders
WHERE DATEDIFF(CURDATE(), order_date) > 30;

注意
DATEDIFF() 返回结果只计算天数，不考虑时间（时、分、秒）。如果想要精确到时间，可以使用 TIMESTAMPDIFF() 函数。