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;
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.