175. 组合两个表

表1: Person

±------------±--------+
| 列名 | 类型 |
±------------±--------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
±------------±--------+
PersonId 是上表主键
表2: Address

±------------±--------+
| 列名 | 类型 |
±------------±--------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
±------------±--------+
AddressId 是上表主键

编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:

FirstName, LastName, City, State

select firstname,lastname,city,state
from person left join address 
where person.personid=address.personid;

image-1648034700745

176. 第二高的薪水

Employee 表:
±------------±-----+
| Column Name | Type |
±------------±-----+
| id | int |
| salary | int |
±------------±-----+
id 是这个表的主键。
表的每一行包含员工的工资信息。

编写一个 SQL 查询,获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水,查询应该返回 null 。

select ifnull(
    (select distinct salary 
    from employee
    order by salary desc
    limit 1,1),null
    )
as SecondHighestSalary
  • ifnull(a,b) 如果a非空,则返回a,如果a为空,则返回b
  • distinct 用于去重

180. 连续出现的数字

表:Logs

±------------±--------+
| Column Name | Type |
±------------±--------+
| id | int |
| num | varchar |
±------------±--------+
id 是这个表的主键。

编写一个 SQL 查询,查找所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。

查询结果格式如下面的例子所示:

示例 1:

输入:
Logs 表:
±—±----+
| Id | Num |
±—±----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
±—±----+
输出:
Result 表:
±----------------+
| ConsecutiveNums |
±----------------+
| 1 |
±----------------+
解释:1 是唯一连续出现至少三次的数字。

select distinct l1.num  ConsecutiveNums
from logs l1, logs l2, logs l3
where l1.id=l2.id-1 and l2.id=l3.id-1 and l1.num=l2.num and l2.num=l3.num;
  • 使用distinct防止连续三次以上出现重复的数字,则会返回前几个结果,所以需要去重

181. 超过经理收入的员工

表:Employee

±------------±--------+
| Column Name | Type |
±------------±--------+
| id | int |
| name | varchar |
| salary | int |
| managerId | int |
±------------±--------+
Id是该表的主键。
该表的每一行都表示雇员的ID、姓名、工资和经理的ID。

编写一个SQL查询来查找收入比经理高的员工。

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例 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 Employee from employee e1 ,employee e2
where e1.salary>e2.salary and e1.managerid=e2.id;

177. 第N高的薪水

表: Employee

±------------±-----+
| Column Name | Type |
±------------±-----+
| id | int |
| salary | int |
±------------±-----+
Id是该表的主键列。
该表的每一行都包含有关员工工资的信息。

编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资,查询应该报告为 null 。

查询结果格式如下所示。

示例 1:

输入:
Employee table:
±—±-------+
| id | salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
±—±-------+
n = 2
输出:
±-----------------------+
| getNthHighestSalary(2) |
±-----------------------+
| 200 |
±-----------------------+
示例 2:

输入:
Employee 表:
±—±-------+
| id | salary |
±—±-------+
| 1 | 100 |
±—±-------+
n = 2
输出:
±-----------------------+
| getNthHighestSalary(2) |
±-----------------------+
| null |
±-----------------------+

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    set N=N-1;
  RETURN (
      # Write your MySQL query statement below
      select ifnull(
          (select distinct salary from employee 
      order by salary desc
      limit N,1),null)      
  );
END
  • limit 后只能加正整数,不能加小数,0,负数,表达式(N-1),所以要在前面定义变量 用set N=N-1;

197. 上升的温度

表: Weather

±--------------±--------+
| Column Name | Type |
±--------------±--------+
| id | int |
| recordDate | date |
| temperature | int |
±--------------±--------+
id 是这个表的主键
该表包含特定日期的温度信息

编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 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  w2.id from weather w1,weather w2
where datediff(w2.recorddate,w1.recorddate)=1 and w1.temperature < w2.temperature

184. 部门工资最高的员工

表: Employee

±-------------±--------+
| 列名 | 类型 |
±-------------±--------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
±-------------±--------+
id是此表的主键列。
departmentId是Department表中ID的外键。
此表的每一行都表示员工的ID、姓名和工资。它还包含他们所在部门的ID。

表: Department

±------------±--------+
| 列名 | 类型 |
±------------±--------+
| id | int |
| name | varchar |
±------------±--------+
id是此表的主键列。
此表的每一行都表示一个部门的ID及其名称。

编写SQL查询以查找每个部门中薪资最高的员工。
按 任意顺序 返回结果表。
查询结果格式如下例所示。

示例 1:

输入:
Employee 表:
±—±------±-------±-------------+
| id | name | salary | departmentId |
±—±------±-------±-------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
±—±------±-------±-------------+
Department 表:
±—±------+
| id | name |
±—±------+
| 1 | IT |
| 2 | Sales |
±—±------+
输出:
±-----------±---------±-------+
| Department | Employee | Salary |
±-----------±---------±-------+
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
| IT | Max | 90000 |
±-----------±---------±-------+

select department.name Department,employee.name Employee,Salary
from employee,department
where employee.departmentid=department.id
and (departmentid,salary) in
(select  employee.departmentid, max(salary)
from employee
group by departmentid);

196. 删除重复的电子邮箱

编写一个 SQL 查询,来删除 Person 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。

±—±-----------------+
| Id | Email |
±—±-----------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
±—±-----------------+
Id 是这个表的主键。
例如,在运行你的查询语句之后,上面的 Person 表应返回以下几行:

±—±-----------------+
| Id | Email |
±—±-----------------+
| 1 | john@example.com |
| 2 | bob@example.com |
±—±-----------------+

delete p1 from
person p1,person p2
where p1.email=p2.email and p1.id>p2.id;

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

select name Customers from customers
where id not in 
(
    select customerid from orders
)

  • 子查询
select name  Customers  from customers c left join orders o
on c.id=o.customerid
where customerid is null;
  • 左连接

182. 查找重复的电子邮箱

编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。

示例:

±—±--------+
| Id | Email |
±—±--------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
±—±--------+
根据以上输入,你的查询应返回以下结果:

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

select email from person 
group by email
having count(email)>1;
  • 重复用分组group by
  • group by后面不能用where,只能用having

577. 员工奖金

选出所有 bonus < 1000 的员工的 name 及其 bonus。

Employee 表单

±------±-------±----------±-------+
| empId | name | supervisor| salary |
±------±-------±----------±-------+
| 1 | John | 3 | 1000 |
| 2 | Dan | 3 | 2000 |
| 3 | Brad | null | 4000 |
| 4 | Thomas | 3 | 4000 |
±------±-------±----------±-------+
empId 是这张表单的主关键字
Bonus 表单

±------±------+
| empId | bonus |
±------±------+
| 2 | 500 |
| 4 | 2000 |
±------±------+
empId 是这张表单的主关键字
输出示例:

±------±------+
| name | bonus |
±------±------+
| John | null |
| Dan | 500 |
| Brad | null |
±------±------+

select name,bonus
from employee e left join bonus b on e.empid=b.empid
where b.bonus<1000 or b.bonus is null;

178. 分数排名

表: Scores

±------------±--------+
| Column Name | Type |
±------------±--------+
| id | int |
| score | decimal |
±------------±--------+
Id是该表的主键。
该表的每一行都包含了一场比赛的分数。Score是一个有两位小数点的浮点值。

编写 SQL 查询对分数进行排序。排名按以下规则计算:

分数应按从高到低排列。
如果两个分数相等,那么两个分数的排名应该相同。
在排名相同的分数后,排名数应该是下一个连续的整数。换句话说,排名之间不应该有空缺的数字。
按 score 降序返回结果表。

查询结果格式如下所示。

示例 1:

输入:
Scores 表:
±—±------+
| id | score |
±—±------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
±—±------+
输出:
±------±-----+
| score | rank |
±------±-----+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
±------±-----+

select score,
        (dense_rank() over (order by score desc)) 'rank'
from scores;      
  • 排名函数
    • rank() over(order by name) 有重复的排名后会不连续
    • dense_rank() over(order by name) 排名后数字会连续

Q.E.D.