目录
175. 组合两个表
题目
表1: Person,PersonId 是上表主键
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
表2: Address,AddressId 是上表主键
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
题目:编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:
FirstName, LastName, City, State
思路:left join
重点在这一句“无论 person 是否有地址信息”,用到左/右连接的知识
select Person.FirstName, Person.LastName, Address.City, Address.State
from Person left join Address
on Address.PersonId=Person.PersonId
176. 第二高的薪水
题目
编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary) 。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null。
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
思路:group by, order by desc, limit offset
- 先按照薪水分组并且降序排序order by xxx desc,然后选取排在第二位的薪水值limit 1 offset 1
- limit n 其实就等于 limit 0,n 或者 limit n offset 0: 这个0表示偏移量
- 如果没找到的话,查询会返回空值,但题意要求返回null值。所以我们利用select NULL会返回null值的特点,建立临时表
# 1、先按照薪水分组并且降序排序order by xxx desc,然后选取排在第二位的薪水值limit 1 offset 1
# limit n 其实就等于 limit 0,n 或者 limit n offset 0: 这个0表示偏移量
# 2、如果没找到的话,查询会返回空值,但题意要求返回null值。所以我们利用select NULL会返回null值的特点,建立临时表
select (select Salary as SecondHighestSalary
from Employee
group by Salary
order by Salary desc
limit 1 offset 1) as SecondHighestSalary
六种方案
177. 第N高的薪水
题目
在第二高的薪水上相当于多了一个输入参数
思路(同)
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N := N-1; # 记得先把n减1
RETURN (
# Write your MySQL query statement below.
select Salary
from Employee
group by Salary
order by Salary desc
limit 1 offset N
);
END
178. 分数排名
题目
编写一个 SQL 查询来实现分数排名。
如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔”。
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
例如,根据上述给定的 Scores 表,你的查询应该返回(按分数从高到低排列):
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
重要提示:对于 MySQL 解决方案,如果要转义用作列名的保留字,可以在关键字之前和之后使用撇号。例如 `Rank`
思路:count(distinct)
- 先按分数降序排序,然后对于每个人,查询比他分数高的分数有多少(去重),这样就能计算出排名啦
- 注意新加的列,列名要用撇号扩起来 `Rank`
# 先按分数降序排序,然后对于每个人,查询比他分数高的分数有多少(去重),这样就能计算出排名啦
select a.Score , (select count(distinct b.Score) from Scores b where b.Score>=a.Score) as 'Rank'
from Scores a
order by a.Score desc
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 a.Name as Employee
from Employee a, Employee b
where a.ManagerId = b.Id and a.Salary > b.Salary
182. 查找重复的电子邮箱
题目
编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。
示例:
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
根据以上输入,你的查询应返回以下结果:
+---------+
| Email |
+---------+
| a@b.com |
+---------+
说明:所有电子邮箱都是小写字母。
思路:group by, having
select Email
from Person
group by Email
having count(*) > 1
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 |
+-----------+
思路: 查找不在表里的数据not in
顾客id没有出现在已购订单order的顾客id中过,即查找不在表里的数据,那就用关键词not in
select a.Name as Customers
from Customers a
where a.id not in (select b.CustomerId from Orders b)