175. 组合两个表
题目
表: Person
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
personId 是该表的主键(具有唯一值的列)。
该表包含一些人的 ID 和他们的姓和名的信息。
表: Address
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
addressId 是该表的主键(具有唯一值的列)。
该表的每一行都包含一个 ID = PersonId 的人的城市和州的信息。
编写解决方案,报告 Person
表中每个人的姓、名、城市和州。如果 personId
的地址不在 Address
表中,则报告为 null
。
答案
select t1.firstName,t1.LastName,t2.city,t2.state
from person t1
left join address t2 using(personId);
176. 第二高的薪水
题目
Employee
表:
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
在 SQL 中,id 是这个表的主键。
表的每一行包含员工的工资信息。
查询并返回 Employee
表中第二高的薪水 。如果不存在第二高的薪水,查询应该返回 null(Pandas 则返回 None)
。
查询结果如下例所示
答案
select
ifnull(
(select distinct salary
from employee
order by salary desc limit 1,1) , null) as SecondHighestSalary;
177. 第N高的薪水
题目
表: Employee
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
在 SQL 中,id 是该表的主键。
该表的每一行都包含有关员工工资的信息。
查询 Employee
表中第 n
高的工资。如果没有第 n
个最高工资,查询结果应该为 null
。
查询结果格式如下所示。
答案
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M = N-1;
RETURN (
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT M, 1
);
END
178. 分数排名
题目
表: Scores
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| score | decimal |
+-------------+---------+
在 SQL 中,id 是该表的主键。
该表的每一行都包含了一场比赛的分数。Score 是一个有两位小数点的浮点值。
查询并对分数进行排序。排名按以下规则计算:
- 分数应按从高到低排列。
- 如果两个分数相等,那么两个分数的排名应该相同。
- 在排名相同的分数后,排名数应该是下一个连续的整数。换句话说,排名之间不应该有空缺的数字。
答案
select t1.score,
(select count(distinct t2.score)
from scores t2
where t2.score>=t1.score
) as `rank`
from scores t1
order by t1.score desc;
180. 连续出现的数字
题目
表:Logs
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| num | varchar |
+-------------+---------+
在 SQL 中,id 是该表的主键。
id 是一个自增列。
找出所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
结果格式如下面的例子所示:
答案
# Write your MySQL query statement below
select distinct t1.num as ConsecutiveNums
from logs t1,logs t2,logs t3
where t1.id+1=t2.id and t2.id+1=t3.id and t1.num=t2.num and t2.num=t3.num;
181. 超过经理收入的员工
题目
表:Employee
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
| salary | int |
| managerId | int |
+-------------+---------+
id 是该表的主键(具有唯一值的列)。
该表的每一行都表示雇员的ID、姓名、工资和经理的ID。
编写解决方案,找出收入比经理高的员工。
答案
# Write your MySQL query statement below
select t1.name as Employee
from Employee t1,Employee t2
where t1.managerId = t2.id and t1.salary > t2.salary;
182. 查找重复的电子邮箱
题目
表: Person
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| email | varchar |
+-------------+---------+
id 是该表的主键(具有唯一值的列)。
此表的每一行都包含一封电子邮件。电子邮件不包含大写字母。
编写解决方案来报告所有重复的电子邮件。 请注意,可以保证电子邮件字段不为 NULL。
答案
# Write your MySQL query statement below
select email as Email
from person
group by email having count(*) >= 2;
183. 从不订购的客户
题目
Customers
表:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
在 SQL 中,id 是该表的主键。
该表的每一行都表示客户的 ID 和名称。
Orders
表:
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| customerId | int |
+-------------+------+
在 SQL 中,id 是该表的主键。
customerId 是 Customers 表中 ID 的外键( Pandas 中的连接键)。
该表的每一行都表示订单的 ID 和订购该订单的客户的 ID。
找出所有从不点任何东西的顾客。
答案
# Write your MySQL query statement below
select t1.name as Customers
from Customers t1
where t1.id not in (select t2.customerId from Orders t2);
184. 部门工资最高的员工
题目
表: Employee
+--------------+---------+
| 列名 | 类型 |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
在 SQL 中,id是此表的主键。
departmentId 是 Department 表中 id 的外键(在 Pandas 中称为 join key)。
此表的每一行都表示员工的 id、姓名和工资。它还包含他们所在部门的 id。
表: Department
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
在 SQL 中,id 是此表的主键列。
此表的每一行都表示一个部门的 id 及其名称。
查找出每个部门中薪资最高的员工。
答案
#错误代码 t2.name 不能分组
select t2.name as Department,t1.name as Employee,t1.salary as salary
from (select * from Employee group by departmentId having max(salary)) t1
inner join Department t2 on t1.departmentId = t2.id;
#正确 先表连接 再用 in 挑选
select t2.name as Department,t1.name as Employee,t1.salary as Salary
from Employee t1
inner join Department t2 on t1.departmentId = t2.id
where (t1.departmentId,t1.salary) in (
select departmentId,max(salary)
from Employee
group by departmentId);