【leetcode database】组合两个表、第二高的薪水、第N高的薪水、分数排名、超过经理收入的员工、查找重复的电子邮箱、 从不订购的客户

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

  1. 先按照薪水分组并且降序排序order by xxx desc,然后选取排在第二位的薪水值limit 1 offset 1
  • limit n 其实就等于 limit 0,n 或者 limit n offset 0: 这个0表示偏移量
  1. 如果没找到的话,查询会返回空值,但题意要求返回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

六种方案

6种方案诠释MySQL通用查询策略

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)
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值