【SQL每日一练】day2：leetcode高频习题练习

题目一：每个产品在不同商店的价格

题目要求：

请你重构 Products 表，查询每个产品在不同商店的价格，使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售，则不输出这一行。

输出结果表中的 顺序不作要求 。

1795. 每个产品在不同商店的价格 - 力扣（LeetCode）

表结构：

 运行结果示例：

思路：

这是非常经典的一道“行转列”的题目，很多笔面试都会考到这类型的题目。其特征就是将现有表的某些字段集合为同一个字段中的不同值。MySQL中并没有直接给我们提供行列互转的相关函数，但是这类型的题目处理起来并不难，我们可以直接查询出每一列的数据，再进行组合。

例如，我们可以先查询出所有stroe1的数据，需要注意的是这里的“store1”需要括起来，即将字符串作为store的值。

select product_id, 'store1' as store, store1 as price
from Products where store1 is not null

如此类推，查询出每一列的数据，再组合即可。

运行代码示例：

# Write your MySQL query statement below
select product_id, 'store1' as store, store1 as price
from Products where store1 is not null
union all
select product_id, 'store2' as store, store2 as price
from Products where store2 is not null
union all
select product_id, 'store3' as store, store3 as price
from Products where store3 is not null;

题目二：按分类统计薪水

题目要求：

查询每个工资类别的银行账户数量。 工资类别如下：

• "Low Salary"：所有工资 严格低于 20000 美元。
• "Average Salary"： 包含 范围内的所有工资 [$20000, $50000] 。

• "High Salary"：所有工资 严格大于 50000 美元。

结果表 必须 包含所有三个类别。 如果某个类别中没有帐户，则报告 0 。

按 任意顺序 返回结果表。

1907. 按分类统计薪水 - 力扣（LeetCode）

表结构：

 运行结果示例：

思路：

这道题目的思想其实和上题类似，我们分别按照统计规律筛选出对应的标签和数量，再进行合并结果集即可。

运行代码示例：

select 'Low Salary' as category,ifnull(count(*),0) as accounts_count
from Accounts
where income < 20000
union
select 'Average Salary' as category,ifnull(count(*),0) as accounts_count
from Accounts
where income <= 50000 and income >= 20000
union
select 'High Salary' as category,ifnull(count(*),0) as accounts_count
from Accounts
where income > 50000 

题目三：2020年最后一次登录

题目要求：

编写解决方案以获取在 2020 年登录过的所有用户的本年度 最后一次 登录时间。结果集 不 包含 2020 年没有登录过的用户。

返回的结果集可以按 任意顺序 排列。

1890. 2020年最后一次登录 - 力扣（LeetCode）

表结构：

 运行结果示例：

思路：

这道题需要我们找到“在2020年登陆过且是最后一次登录”的时间戳，我们只要限制登录时间为2020年期间，再找到这期间内的最大值（最后一次时间）即可。

运行代码示例：

select user_id,max(time_stamp) as last_stamp
from Logins
where time_stamp between '2020-01-01 00:00:00' and '2020-12-31 23:59:59'
-- WHERE year(time_stamp) = '2020'  
group by user_id

题目四：计算特殊奖金

题目要求：

编写解决方案，计算每个雇员的奖金。如果一个雇员的 id 是 奇数 并且他的名字不是以 'M' 开头，那么他的奖金是他工资的 100% ，否则奖金为 0 。

返回的结果按照 employee_id 排序。

1873. 计算特殊奖金 - 力扣（LeetCode）

表结构：

 运行结果示例：

思路：

本题考察的是case语句或者if判断语句的使用，整体难度不高。我们只需要进行一个条件判断即可，方法很多。

-- 对姓名的判断方法
-- 1.like模糊匹配
name not like 'M%' 
-- 2.left函数
LEFT(name,1) ！='M'
-- 3.正则匹配
name not rlike '^M'

-- 对id的判断方法
-- 1.取余，除2余数等于1
employee_id%2=1
-- 2.mod（）函数取余
MOD(employee_id,2)!=0

运行代码示例：

-- case子句写法
select employee_id,
case when employee_id % 2 = 1 and name not like 'M%' 
then salary else 0 end as bonus 
from Employees
order by employee_id
-- if判断写法
select employee_id,if(
        employee_id%2=1 and name not like 'M%',
        salary,
        0
    ) as bonus
from employees
order by employee_id;

题目五：丢失信息的雇员

题目要求：

编写解决方案，找到所有 丢失信息 的雇员 id。当满足下面一个条件时，就被认为是雇员的信息丢失：

• 雇员的 姓名 丢失了，或者
• 雇员的 薪水信息 丢失了

返回这些雇员的 id  employee_id ， 从小到大排序 。

表结构：

 运行结果示例：

思路：

这道题其实使用全外连接会非常简单，类似下面的示例：

select distinct employee_id 
from Employees e 
full join Salaries s
on e.employee_id = s.employee_id 
where name is null or salary is null

但是MySQL并不支持这种写法，所以我们可以转换思路，分别使用左外连接和右外连接来查询，最后合并结果集。

运行代码示例：

select distinct e.employee_id 
from Employees e 
left join Salaries s
on e.employee_id = s.employee_id 
where salary is null 
union 
select distinct s.employee_id 
from Employees e 
right join Salaries s
on e.employee_id = s.employee_id 
where name is null 
order by employee_id

题目六 ：排名靠前的旅行者

题目要求：

编写解决方案，报告每个用户的旅行距离。

返回的结果表单，以 travelled_distance 降序排列 ，如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。

1407. 排名靠前的旅行者 - 力扣（LeetCode）

表结构：

 运行结果示例：

思路：

本题考察了分组函数的使用，我们只需连接两表，使用sum（）函数计算出每个用户的行程总和（注意对空值的处理），再按规则进行排序即可。

运行代码示例：

select u.name,ifnull(sum(distance),0) as travelled_distance
from Users u 
left join Rides r 
on u.id = r.user_id
group by user_id
order by travelled_distance desc, name