177. 第N高的薪水
难度中等401
编写一个 SQL 查询,获取 Employee
表中第 n 高的薪水(Salary)。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee
表,n = 2 时,应返回第二高的薪水 200
。如果不存在第 n 高的薪水,那么查询应返回 null
。
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee(Id INT,Salary INT);
INSERT INTO Employee(Id,Salary) VALUES(1,100);
INSERT INTO Employee(Id,Salary) VALUES(2,200);
INSERT INTO Employee(Id,Salary) VALUES(3,300);
DROP FUNCTION IF EXISTS getNthHighestSalary;
DELIMITER $
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE num INT DEFAULT N-1;
RETURN (
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT num,1
# Write your MySQL query statement below.
);
END $
DELIMITER ;
SELECT getNthHighestSalary(2);
180. 连续出现的数字
难度中等361
SQL架构
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如,给定上面的 Logs
表, 1
是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
drop table IF EXISTS Logs;
Create table If Not Exists Logs (Id int, Num int);
Truncate table Logs;
insert into Logs (Id, Num) values ('1', '1');
insert into Logs (Id, Num) values ('2', '1');
insert into Logs (Id, Num) values ('3', '1');
insert into Logs (Id, Num) values ('4', '2');
insert into Logs (Id, Num) values ('5', '1');
insert into Logs (Id, Num) values ('6', '2');
insert into Logs (Id, Num) values ('7', '2');
SELECT distinct l1.Num ConsecutiveNums
FROM logs l1,logs l2,logs l3
where l1.Id+1=l2.Id and l2.Id+1=l3.Id and l1.Num=l2.Num and l2.Num=l3.Num
184. 部门工资最高的员工
难度中等330
SQL架构
Employee
表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| 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 |
+----+----------+
编写一个 SQL 查询,找出每个部门工资最高的员工。对于上述表,您的 SQL 查询应返回以下行(行的顺序无关紧要)。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS Department;
Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int);
Create table If Not Exists Department (Id int, Name varchar(255));
Truncate table Employee;
insert into Employee (Id, Name, Salary, DepartmentId) values ('1', 'Joe', '70000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('2', 'Jim', '90000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('3', 'Henry', '80000', '2');
insert into Employee (Id, Name, Salary, DepartmentId) values ('4', 'Sam', '60000', '2');
insert into Employee (Id, Name, Salary, DepartmentId) values ('5', 'Max', '90000', '1');
Truncate table Department;
insert into Department (Id, Name) values ('1', 'IT');
insert into Department (Id, Name) values ('2', 'Sales');
#方法一
SELECT d1.Name Department,e1.Name Employee,e1.Salary
FROM employee e1,department d1
where e1.DepartmentId=d1.Id
and e1.Salary>=all(
select e2.Salary
from employee e2,department d2
where e2.DepartmentId=d2.Id
and e2.DepartmentId=e1.DepartmentId
)
方法二
SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM
Employee
JOIN
Department ON Employee.DepartmentId = Department.Id
WHERE
(Employee.DepartmentId , Salary) IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
;
178. 分数排名
难度中等677
SQL架构
编写一个 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 |
+-------+------+
drop TABLE IF EXISTS Scores;
Create table If Not Exists Scores (Id int, Score DECIMAL(3,2));
Truncate table Scores;
insert into Scores (Id, Score) values ('1', '3.5');
insert into Scores (Id, Score) values ('2', '3.65');
insert into Scores (Id, Score) values ('3', '4.0');
insert into Scores (Id, Score) values ('4', '3.85');
insert into Scores (Id, Score) values ('5', '4.0');
insert into Scores (Id, Score) values ('6', '3.65');
SELECT Score, CASE
WHEN @pre = Score THEN @rk + 0
WHEN @pre := Score THEN @rk := @rk + 1
ELSE @rk := @rk + 1
END AS `Rank`
FROM Scores, (SELECT @pre := NULL, @rk := 0) AS init
ORDER BY Score DESC;
select a.Score Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) 'Rank'
from Scores a
order by a.Score DESC
626. 换座位
难度中等218
SQL架构
小美是一所中学的信息科技老师,她有一张 seat
座位表,平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢?
示例:
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如数据输入的是上表,则输出结果如下:
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
Create table If Not Exists seat(id int, student varchar(255));
Truncate table seat;
insert into seat (id, student) values ('1', 'Abbot');
insert into seat (id, student) values ('2', 'Doris');
insert into seat (id, student) values ('3', 'Emerson');
insert into seat (id, student) values ('4', 'Green');
insert into seat (id, student) values ('5', 'Jeames');
#交换id
select case when mod(id,2)=1 and id!=s2.cnt then id+1
when mod(id,2)=0 then id-1
else id
end id,student
from seat s1,
(select count(*) cnt
from seat) s2
order by id
1045. 买下所有产品的客户
难度中等17
SQL架构
Customer
表:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| customer_id | int |
| product_key | int |
+-------------+---------+
product_key 是 Customer 表的外键。
Product
表:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| product_key | int |
+-------------+---------+
product_key 是这张表的主键。
写一条 SQL 查询语句,从 Customer
表中查询购买了 Product
表中所有产品的客户的 id。
示例:
Customer 表:
+-------------+-------------+
| customer_id | product_key |
+-------------+-------------+
| 1 | 5 |
| 2 | 6 |
| 3 | 5 |
| 3 | 6 |
| 1 | 6 |
+-------------+-------------+
Product 表:
+-------------+
| product_key |
+-------------+
| 5 |
| 6 |
+-------------+
Result 表:
+-------------+
| customer_id |
+-------------+
| 1 |
| 3 |
+-------------+
购买了所有产品(5 和 6)的客户的 id 是 1 和 3 。
#不存在这样的商品,该顾客没有买
SELECT DISTINCT c.customer_id
FROM (SELECT DISTINCT customer_id FROM customer) c
where not exists
(
SELECT * FROM product p
WHERE not exists(
SELECT * FROM customer c1
where c1.product_key=p.product_key and c1.customer_id=c.customer_id
)
);
608. 树节点
难度中等35
SQL架构
给定一个表 tree
,id 是树节点的编号, p_id 是它父节点的 id 。
+----+------+
| id | p_id |
+----+------+
| 1 | null |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
+----+------+
树中每个节点属于以下三种类型之一:
- 叶子:如果这个节点没有任何孩子节点。
- 根:如果这个节点是整棵树的根,即没有父节点。
- 内部节点:如果这个节点既不是叶子节点也不是根节点。
写一个查询语句,输出所有节点的编号和节点的类型,并将结果按照节点编号排序。上面样例的结果为:
+----+------+
| id | Type |
+----+------+
| 1 | Root |
| 2 | Inner|
| 3 | Leaf |
| 4 | Leaf |
| 5 | Leaf |
+----+------+
解释
-
节点 ‘1’ 是根节点,因为它的父节点是 NULL ,同时它有孩子节点 ‘2’ 和 ‘3’ 。
-
节点 ‘2’ 是内部节点,因为它有父节点 ‘1’ ,也有孩子节点 ‘4’ 和 ‘5’ 。
-
节点 ‘3’, ‘4’ 和 ‘5’ 都是叶子节点,因为它们都有父节点同时没有孩子节点。
-
样例中树的形态如下:
1 / \ 2 3 / \ 4 5
Create table If Not Exists tree (id int, p_id int);
Truncate table tree;
insert into tree (id, p_id) values ('1', null);
insert into tree (id, p_id) values ('2', '1');
insert into tree (id, p_id) values ('3', '1');
insert into tree (id, p_id) values ('4', '2');
insert into tree (id, p_id) values ('5', '2');
#根节点pid是null,叶子节点不应该是别人的pid,其他节点是分支节点
SELECT
id,
CASE WHEN id = (SELECT id
FROM tree
WHERE p_id IS NULL)
THEN 'Root'
WHEN id NOT IN (SELECT p_id
FROM tree WHERE p_id is not NULL )
THEN 'Leaf'
ELSE 'Inner'
END Type
FROM tree
ORDER BY id
1112. 每位学生的最高成绩
难度中等14
SQL架构
表:Enrollments
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| student_id | int |
| course_id | int |
| grade | int |
+---------------+---------+
(student_id, course_id) 是该表的主键。
编写一个 SQL 查询,查询每位学生获得的最高成绩和它所对应的科目,若科目成绩并列,取 course_id
最小的一门。查询结果需按 student_id
增序进行排序。
查询结果格式如下所示:
Enrollments 表:
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 2 | 2 | 95 |
| 2 | 3 | 95 |
| 1 | 1 | 90 |
| 1 | 2 | 99 |
| 3 | 1 | 80 |
| 3 | 2 | 75 |
| 3 | 3 | 82 |
+------------+-----------+-------+
Result 表:
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 1 | 2 | 99 |
| 2 | 2 | 95 |
| 3 | 3 | 82 |
+------------+-----------+-------+
Create table If Not Exists Enrollments (student_id int, course_id int, grade int);
Truncate table Enrollments;
insert into Enrollments (student_id, course_id, grade) values ('2', '2', '95');
insert into Enrollments (student_id, course_id, grade) values ('2', '3', '95');
insert into Enrollments (student_id, course_id, grade) values ('1', '1', '90');
insert into Enrollments (student_id, course_id, grade) values ('1', '2', '99');
insert into Enrollments (student_id, course_id, grade) values ('3', '1', '80');
insert into Enrollments (student_id, course_id, grade) values ('3', '2', '75');
insert into Enrollments (student_id, course_id, grade) values ('3', '3', '82');
SELECT student_id,MIN(course_id) course_id,MAX(grade) grade
FROM enrollments
WHERE (student_id,grade) IN
(SELECT student_id,max(grade)
FROM enrollments
GROUP BY student_id)
GROUP BY student_id
ORDER BY student_id ;
1398 购买了产品 A 和产品 B 却没有购买产品 C 的顾客
Customers 表:
+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| customer_id | int |
| customer_name | varchar |
+---------------------+---------+
customer_id 是这张表的主键。
customer_name 是顾客的名称。
Orders 表:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| customer_id | int |
| product_name | varchar |
+---------------+---------+
order_id 是这张表的主键。
customer_id 是购买了名为 "product_name" 产品顾客的id。
请你设计 SQL 查询来报告购买了产品 A 和产品 B 却没有购买产品 C 的顾客的 ID 和姓名( customer_id 和 customer_name ),我们将基于此结果为他们推荐产品 C 。
您返回的查询结果需要按照 customer_id 排序。
查询结果如下例所示。
Customers table:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1 | Daniel |
| 2 | Diana |
| 3 | Elizabeth |
| 4 | Jhon |
+-------------+---------------+
Orders table:
+------------+--------------+---------------+
| order_id | customer_id | product_name |
+------------+--------------+---------------+
| 10 | 1 | A |
| 20 | 1 | B |
| 30 | 1 | D |
| 40 | 1 | C |
| 50 | 2 | A |
| 60 | 3 | A |
| 70 | 3 | B |
| 80 | 3 | D |
| 90 | 4 | C |
+------------+--------------+---------------+
Result table:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 3 | Elizabeth |
+-------------+---------------+
只有 customer_id 为 3 的顾客购买了产品 A 和产品 B ,却没有购买产品 C 。
select c1.*
from Customers c1,Orders o1,Orders o2
where c1.customer_id=o1.customer_id and c1.customer_id=o2.customer_id
and o1.product_name='A' and o2.product_name='B'
and c1.customer_id not in(
select Customers.customer_id
from Customers,Orders
where Customers.customer_id=Orders.customer_id
and product_name='C'
)
1264. 页面推荐
朋友关系列表: Friendship
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user1_id | int |
| user2_id | int |
+---------------+---------+
这张表的主键是 (user1_id, user2_id)。
这张表的每一行代表着 user1_id 和 user2_id 之间存在着朋友关系。
喜欢列表: Likes
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| page_id | int |
+-------------+---------+
这张表的主键是 (user_id, page_id)。
这张表的每一行代表着 user_id 喜欢 page_id。
写一段 SQL 向user_id
= 1 的用户,推荐其朋友们喜欢的页面。不要推荐该用户已经喜欢的页面。
Friendship table:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 3 |
| 2 | 4 |
| 2 | 5 |
| 6 | 1 |
+----------+----------+
Likes table:
+---------+---------+
| user_id | page_id |
+---------+---------+
| 1 | 88 |
| 2 | 23 |
| 3 | 24 |
| 4 | 56 |
| 5 | 11 |
| 6 | 33 |
| 2 | 77 |
| 3 | 77 |
| 6 | 88 |
+---------+---------+
Result table:
+------------------+
| recommended_page |
+------------------+
| 23 |
| 24 |
| 56 |
| 33 |
| 77 |
+------------------+
用户1 同 用户2, 3, 4, 6 是朋友关系。
推荐页面为: 页面23 来自于 用户2, 页面24 来自于 用户3, 页面56 来自于 用户3 以及 页面33 来自于 用户6。
页面77 同时被 用户2 和 用户3 推荐。
页面88 没有被推荐,因为 用户1 已经喜欢了它。
#方法1
select distinct t2.page_id recommended_page
from
(select user2_id id
from Friendship
where user1_id=1
union
select user1_id id
from Friendship
where user2_id=1
) t1,Likes t2
where t2.user_id=t1.id
and t2.page_id not in(
select page_id
from
Likes
where user_id=1
)
#这里的union可以被优化
SELECT DISTINCT page_id AS recommended_page
FROM Likes
WHERE user_id IN (
SELECT (
CASE
WHEN user1_id = 1 then user2_id
WHEN user2_id = 1 then user1_id
END
) AS user_id
FROM Friendship
WHERE user1_id = 1 OR user2_id = 1
) AND page_id NOT IN (
SELECT page_id FROM Likes WHERE user_id = 1
)
1270. 向公司CEO汇报工作的所有人
员工表:Employees
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| employee_id | int |
| employee_name | varchar |
| manager_id | int |
+---------------+---------+
employee_id 是这个表的主键。
这个表中每一行中,employee_id 表示职工的 ID,employee_name 表示职工的名字,manager_id 表示该职工汇报工作的直线经理。
这个公司 CEO 是 employee_id = 1 的人。
用 SQL 查询出所有直接或间接向公司 CEO 汇报工作的职工的 employee_id 。
由于公司规模较小,经理之间的间接关系不超过 3 个经理。
可以以任何顺序返回的结果,不需要去重。
查询结果示例如下:
Employees table:
+-------------+---------------+------------+
| employee_id | employee_name | manager_id |
+-------------+---------------+------------+
| 1 | Boss | 1 |
| 3 | Alice | 3 |
| 2 | Bob | 1 |
| 4 | Daniel | 2 |
| 7 | Luis | 4 |
| 8 | Jhon | 3 |
| 9 | Angela | 8 |
| 77 | Robert | 1 |
+-------------+---------------+------------+
Result table:
+-------------+
| employee_id |
+-------------+
| 2 |
| 77 |
| 4 |
| 7 |
+-------------+
公司 CEO 的 employee_id 是 1.
employee_id 是 2 和 77 的职员直接汇报给公司 CEO。
employee_id 是 4 的职员间接汇报给公司 CEO 4 --> 2 --> 1 。
employee_id 是 7 的职员间接汇报给公司 CEO 7 --> 4 --> 2 --> 1 。
employee_id 是 3, 8 ,9 的职员不会直接或间接的汇报给公司 CEO。
#方法一,三个union
select employee_id
from Employees
where employee_id in
(
select e.employee_id
from Employees e
where e.manager_id=1
union
select e1.employee_id
from Employees e1,Employees e2
where e1.manager_id=e2.employee_id
and e2.manager_id=1
union
select e3.employee_id
from Employees e3,Employees e4,Employees e5
where e3.manager_id=e4.employee_id
and e4.manager_id=e5.employee_id
and e5.manager_id=1
) and employee_id<>1
#方法二,三表连接
SELECT e1.employee_id
FROM Employees e1
JOIN Employees e2 ON e1.manager_id = e2.employee_id
JOIN Employees e3 ON e2.manager_id = e3.employee_id
WHERE e1.employee_id != 1 AND e3.manager_id = 1
1077. 项目员工 III
难度中等18
SQL架构
项目表 Project
:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+
(project_id, employee_id) 是这个表的主键
employee_id 是员工表 Employee 的外键
员工表 Employee
:
+------------------+---------+
| Column Name | Type |
+------------------+---------+
| employee_id | int |
| name | varchar |
| experience_years | int |
+------------------+---------+
employee_id 是这个表的主键
写 一个 SQL 查询语句,报告在每一个项目中经验最丰富的雇员是谁。如果出现经验年数相同的情况,请报告所有具有最大经验年数的员工。
Project 表:
+-------------+-------------+
| project_id | employee_id |
+-------------+-------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
+-------------+-------------+
Employee 表:
+-------------+--------+------------------+
| employee_id | name | experience_years |
+-------------+--------+------------------+
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 3 |
| 4 | Doe | 2 |
+-------------+--------+------------------+
Result 表:
+-------------+---------------+
| project_id | employee_id |
+-------------+---------------+
| 1 | 1 |
| 1 | 3 |
| 2 | 1 |
+-------------+---------------+
employee_id 为 1 和 3 的员工在 project_id 为 1 的项目中拥有最丰富的经验。在 project_id 为 2 的项目中,employee_id 为 1 的员工拥有最丰富的经验。
select project_id,e.employee_id from Project p inner join Employee e
on p.employee_id=e.employee_id
where (project_id,experience_years) in
(select project_id, max(experience_years) ey from Project p1 inner join Employee e1
on p1.employee_id=e1.employee_id
group by project_id)