1. 力扣177:第N高的薪水
1.1 题目:
表: Employee
+-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ 在 SQL 中,id 是该表的主键。 该表的每一行都包含有关员工工资的信息。
查询 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 | +------------------------+
1.2 思路:
第N高的薪水,需要distinct过滤掉相同的薪水记录。降序limit取值即可。
1.3 题解:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set n = N-1;
RETURN (
# Write your MySQL query statement below.
select distinct salary
from Employee
order by salary desc
limit n, 1
);
END
2. 力扣178:分数排名
2.1 题目:
表: Scores
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | score | decimal | +-------------+---------+ id 是该表的主键(有不同值的列)。 该表的每一行都包含了一场比赛的分数。Score 是一个有两位小数点的浮点值。
编写一个解决方案来查询分数的排名。排名按以下规则计算:
- 分数应按从高到低排列。
- 如果两个分数相等,那么两个分数的排名应该相同。
- 在排名相同的分数后,排名数应该是下一个连续的整数。换句话说,排名之间不应该有空缺的数字。
按 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 | +-------+------+
2.2 思路:
分数相同,排名相同,第三条记录是2,则可以考虑dense_rank窗口函数。
2.3 题解:
select score, dense_rank() over (order by score desc) `rank`
from Scores ;
3. 力扣511:游戏玩法分析1
3.1 题目:
活动表 Activity
:
+--------------+---------+ | Column Name | Type | +--------------+---------+ | player_id | int | | device_id | int | | event_date | date | | games_played | int | +--------------+---------+ 在 SQL 中,表的主键是 (player_id, event_date)。 这张表展示了一些游戏玩家在游戏平台上的行为活动。 每行数据记录了一名玩家在退出平台之前,当天使用同一台设备登录平台后打开的游戏的数目(可能是 0 个)。
查询每位玩家 第一次登录平台的日期。
查询结果的格式如下所示:
Activity 表: +-----------+-----------+------------+--------------+ | player_id | device_id | event_date | games_played | +-----------+-----------+------------+--------------+ | 1 | 2 | 2016-03-01 | 5 | | 1 | 2 | 2016-05-02 | 6 | | 2 | 3 | 2017-06-25 | 1 | | 3 | 1 | 2016-03-02 | 0 | | 3 | 4 | 2018-07-03 | 5 | +-----------+-----------+------------+--------------+ Result 表: +-----------+-------------+ | player_id | first_login | +-----------+-------------+ | 1 | 2016-03-01 | | 2 | 2017-06-25 | | 3 | 2016-03-02 | +-----------+-------------+
3.2 思路:
相关子查询,内查询查的是外层记录(同一个人)登录平台的行为,顺序排序返回最小的日期,如果外层的记录的日期是其登录平台的最小日期,就不被过滤。
3.3 题解:
select player_id, event_date first_login
from Activity a1
where event_date = (
select event_date
from Activity a2
where a1.player_id = a2.player_id
order by event_date
limit 0, 1
)
4. 力扣 596:超过5位学生的课
4.1 题目:
表: Courses
+-------------+---------+ | Column Name | Type | +-------------+---------+ | student | varchar | | class | varchar | +-------------+---------+ (student, class)是该表的主键(不同值的列的组合)。 该表的每一行表示学生的名字和他们注册的班级。
查询 至少有 5 个学生 的所有班级。
以 任意顺序 返回结果表。
结果格式如下所示。
示例 1:
输入: Courses table: +---------+----------+ | student | class | +---------+----------+ | A | Math | | B | English | | C | Math | | D | Biology | | E | Math | | F | Computer | | G | Math | | H | Math | | I | Math | +---------+----------+ 输出: +---------+ | class | +---------+ | Math | +---------+ 解释: -数学课有 6 个学生,所以我们包括它。 -英语课有 1 名学生,所以我们不包括它。 -生物课有 1 名学生,所以我们不包括它。 -计算机课有 1 个学生,所以我们不包括它。
4.2 思路:
分组having过滤。
4.3 题解:
select class
from Courses
group by class
having count(*) >= 5
5. 力扣627:变更性别
5.1 题目:
Salary
表:
+-------------+----------+ | Column Name | Type | +-------------+----------+ | id | int | | name | varchar | | sex | ENUM | | salary | int | +-------------+----------+ id 是这个表的主键(具有唯一值的列)。 sex 这一列的值是 ENUM 类型,只能从 ('m', 'f') 中取。 本表包含公司雇员的信息。
请你编写一个解决方案来交换所有的 'f'
和 'm'
(即,将所有 'f'
变为 'm'
,反之亦然),仅使用 单个 update 语句 ,且不产生中间临时表。
注意,你必须仅使用一条 update 语句,且 不能 使用 select 语句。
结果如下例所示。
示例 1:
输入: Salary 表: +----+------+-----+--------+ | id | name | sex | salary | +----+------+-----+--------+ | 1 | A | m | 2500 | | 2 | B | f | 1500 | | 3 | C | m | 5500 | | 4 | D | f | 500 | +----+------+-----+--------+ 输出: +----+------+-----+--------+ | id | name | sex | salary | +----+------+-----+--------+ | 1 | A | f | 2500 | | 2 | B | m | 1500 | | 3 | C | f | 5500 | | 4 | D | m | 500 | +----+------+-----+--------+ 解释: (1, A) 和 (3, C) 从 'm' 变为 'f' 。 (2, B) 和 (4, D) 从 'f' 变为 'm' 。
5.2 思路:
if函数,如果condition是真,则返回第二个参数,否则返回第三个参数。
5.3 题解:
update Salary
set sex = if(sex = 'm', 'f', 'm');
6. 力扣1075:项目员工1
6.1 题目:
目表 Project
:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+
主键为 (project_id, employee_id)。
employee_id 是员工表 Employee 表的外键。
这张表的每一行表示 employee_id 的员工正在 project_id 的项目上工作。
员工表 Employee
:
+------------------+---------+ | Column Name | Type | +------------------+---------+ | employee_id | int | | name | varchar | | experience_years | int | +------------------+---------+ 主键是 employee_id。数据保证 experience_years 非空。 这张表的每一行包含一个员工的信息。
请写一个 SQL 语句,查询每一个项目中员工的 平均 工作年限,精确到小数点后两位。
以 任意 顺序返回结果表。
查询结果的格式如下。
示例 1:
输入: 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 | 1 | | 4 | Doe | 2 | +-------------+--------+------------------+ 输出: +-------------+---------------+ | project_id | average_years | +-------------+---------------+ | 1 | 2.00 | | 2 | 2.50 | +-------------+---------------+ 解释:第一个项目中,员工的平均工作年限是 (3 + 2 + 1) / 3 = 2.00;第二个项目中,员工的平均工作年限是 (3 + 2) / 2 = 2.50
6.2 思路:
内查询,将两装表关联起来。
以项目分组-> 求每个分组的平均值。
平均值=sum总和 / 个数。
6.3 题解:
select project_id, round(sum(experience_years) / count(experience_years), 2) average_years
from Project p join Employee e
where p.employee_id = e.employee_id
group by project_id