每日一道Leetcode算法——sql题——2019.02.15

关注

原创最新推荐文章于 2026-08-23 10:01:26 发布 503 阅读 AI 写同款· GEO 优化 ›

LeetCode面试经典150题解析与高效刷题指南算法与数据结构是程序员技术面试的核心考察点，掌握常见算法思想如动态规划、二分查找等能显著提升解题效率。通过系统化的刷题训练，不仅可以深入理解这些基础概念的工作原理，还能培养工程实践中的问题解决能力。LeetCode面试经典150题精选高频考点，覆盖数据结构应用、算法思维等关键领域，特别适合用于技术面试准备。本文结合大厂面试官经验，详解题库结构与核心考点，并提供三阶训练体系、错题管理等实用方法论，帮助开发者高效提升算法能力与面试表现。阅读详情

一题：Combine Two Tables

中文：

Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId is the primary key column for this table.

Table: Address

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId is the primary key column for this table.
为报告编写SQL查询，该报告为Person表中的每个人提供以下信息，无论这个人是否有一个地址：
FirstName, LastName, City, State

英文：

Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId is the primary key column for this table.

Table: Address

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+

AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:
FirstName, LastName, City, State

解题思路：

因为不管用户有没有对应的地址，一直要保证有用户数据，所以以person为左表，进行左关联。

# 创建Person表
Create table Person (PersonId int, FirstName varchar(255), LastName varchar(255));
# 创建Address表
Create table Address (AddressId int, PersonId int, City varchar(255), State varchar(255));
# 删除Person表中的所有数据，类似于delete from Person
Truncate table Person;
# 往Person表中插入一条数据
insert into Person (PersonId, LastName, FirstName) values ('1', 'Wang', 'Allen');
# 删除Address表中的所有数据
Truncate table Address;
# 往Address表中插入一条数据
insert into Address (AddressId, PersonId, City, State) values ('1', '2', 'New York City', 'New York');

# Date：2019.02.15
# num：195
# 解题答案：
# ==============================================================================
# 使用左关联查询表中的数据
SELECT p.FirstName,p.LastName,a.City,a.State from Person p left join Address a on p.PersonId = a.PersonId;

二题：Employees Earning More Than Their Managers

中文：

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      |
+----------+

英文：

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

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

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

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

解题思路：

将表自身通过ManagerId进行关联，前半部分为自身信息，后半部分为经理信息，查询自身薪水大于经理薪水。

并将查询到的name重命名为Employee。

# 创建员工表，有id，名字，薪水，经理Id
CREATE TABLE
IF
	NOT EXISTS Employee ( Id INT, NAME VARCHAR ( 255 ), Salary INT, ManagerId INT );# 清空员工表中所有数据
TRUNCATE TABLE Employee;# 向员工表中插入4条数据
INSERT INTO Employee ( Id, NAME, Salary, ManagerId )
VALUES
	( '1', 'Joe', '70000', '3' );
INSERT INTO Employee ( Id, NAME, Salary, ManagerId )
VALUES
	( '2', 'Henry', '80000', '4' );
INSERT INTO Employee ( Id, NAME, Salary, ManagerId )
VALUES
	( '3', 'Sam', '60000', '0' );
INSERT INTO Employee ( Id, NAME, Salary, ManagerId )
VALUES
	( '4', 'Max', '90000', '0' );
DROP TABLE Employee;# date：2019.02.15
# num：181
# 解题答案：
# =====================================
# 将表自身通过ManagerId进行关联，前半部分为自身信息，后半部分为经理信息，查询自身薪水大于经理薪水
# 并将查询到的name重命名为Employee
SELECT
	A.`Name` AS Employee 
FROM
	Employee AS A,
	Employee AS B 
WHERE
	A.ManagerId = B.Id 
	AND A.Salary > B.Salary;

三题：Customers Who Never Order

中文：

假设一个网站包含两个表，Customers表和Orders表。编写SQL查询以查找从未订购任何内容的所有客户。

Table: Customers.

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Table: Orders.

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

使用上面的表作为示例，返回以下内容：

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

英文：

Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything.

Table: Customers.

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Table: Orders.

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

Using the above tables as example, return the following:

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

解题思路：

第一种：

使用子查询。

查询在订单表中有的customerId作为查询条件，查询Customer表中Id不在其中的用户名称。

第二种：

使用左关联，将Customers和Orders表使用customerId进行关联，如果该用户不存在订单表中，则customerId为空。

# 创建客户表
CREATE TABLE
IF
	NOT EXISTS Customers ( Id INT, NAME VARCHAR ( 255 ) );
# 创建订单表
CREATE TABLE
IF
	NOT EXISTS Orders ( Id INT, CustomerId INT );
# 清空客户表原有数据，并插入数据
TRUNCATE TABLE Customers;
INSERT INTO Customers ( Id, NAME )
VALUES
	( '1', 'Joe' );
INSERT INTO Customers ( Id, NAME )
VALUES
	( '2', 'Henry' );
INSERT INTO Customers ( Id, NAME )
VALUES
	( '3', 'Sam' );
INSERT INTO Customers ( Id, NAME )
VALUES
	( '4', 'Max' );
# 清空订单表原有数据，并插入数据
TRUNCATE TABLE Orders;
INSERT INTO Orders ( Id, CustomerId )
VALUES
	( '1', '3' );
INSERT INTO Orders ( Id, CustomerId )
VALUES
	( '2', '1' );
# date: 2039.02.15
# num: 183
# 解题答案：
# =====================================
# 方法一：使用子查询。查询在订单表中有的customerId作为查询条件，查询Customer表中Id不在其中的用户名称。
SELECT NAME AS
	Customers 
FROM
	Customers 
WHERE
	Id NOT IN ( SELECT CustomerId FROM Orders );
	
# 方法二：使用左关联，将Customers和Orders表使用customerId进行关联，如果该用户不存在订单表中，则customerId为空。
SELECT
	* 
FROM
	Customers C
	LEFT JOIN Orders O ON C.Id = O.CustomerId 
WHERE
	O.CustomerId IS NULL;

收录于