【力扣-SQL】非会员剩余题刷题知识点总结

Cheeryang_ego

已于 2023-06-08 09:49:04 修改

阅读量294

点赞数

分类专栏： # SQL 文章标签： sql leetcode 数据库 mysql Powered by 金山文档

于 2023-03-14 02:24:34 首次发布

本文链接：https://blog.csdn.net/qq_43629945/article/details/129479064

版权

SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

之前刷完了SQL入门的十天打卡计划，链接如下：https://leetcode.cn/study-plan/sql/?progress=jgmzq5s，刷题知识点总结在：【力扣-SQL入门】10天刷题知识点总结

这篇文章主要记录一下剩下的、非会员的SQL题（一共8题），大多难度都是middle和hard，从中学到了很多新的函数和用法，值得记录一下~

626. 换座位（middle）

Q：表Seat有两列，id（主键，是一个连续的增量）和name

交换两个连续学生的座位号，如果学生的数量是奇数，则最后一个学生的id不交换。按id升序返回

分析：交换两个连续学生的座位号，如果把student列（即姓名列）视为不动的话，则id原本为奇数的，交换后若还要对应原先的姓名，id会+1；而id原本为偶数的，会-1

select 
case
    when id%2=1 and id<cnt then id+1
    when id%2=0 then id-1
    else id  
end as id,
student
from Seat, (select max(id) as cnt from Seat) tmp
order by id

注意then后面直接写id+1或者id-1之类的，不能写成id=id+1

262. 行程和用户（hard）

Q：两个表

表Trips（存所有出租车的行程信息）有六列：

id：主键

client_id：Users表中user_id的外键

driver_id：Users表中user_id的外键

city_id

status：表示行程状态，枚举类型，取值为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)

request_at：date

表Users（存所有用户）有三列：

users_id：主键

banned：表示用户是否被禁止，枚举类型，取值为 (‘Yes’, ‘No’)

role：表示用户身份，枚举类型，取值为 (‘client’, ‘driver’, ‘partner’)

返回示例如下：

代码如下：

select request_at as Day,
round(count(if(status!='completed',status,null))/count(*),2) as 'Cancellation Rate'   # 注意别名加引号
from Trips
where request_at between '2013-10-01' and '2013-10-03' and
client_id not in (select users_id from Users where banned='Yes') and
driver_id not in (select users_id from Users where banned='Yes')
group by request_at

四舍五入：round(value, n)

参考： mysql中的round函数

round(value, n) value指要处理的数，n指保留几位小数（n可以是负数，这时是指定小数点左边的n位整数位为0，同时小数位均为0）

round(value) 其实就是 round(value, 0) ，也就是默认n为0

sum()和count()中使用if判断条件

参考：使用SQL语句统计数据时sum和count函数中使用if判断条件

sum函数中使用if判断条件格式为：sum(if(条件,列值,0))

注解：sum是求和函数，条件为真时，执行列值（字段名）求和也就是累加，条件为假时为0求和（当然还是0）

1.单条件判断格式，sum(if(条件字段名=值,需要计算sum的字段名,0))

2.多条件判断格式，sum(if(条件字段名>值 AND 条件字段名>值 AND 条件字段名=值,1,0))

注解：多条件判断格式整体含义为，计算满足条件的数据总数，如果满足条件，那么数据总数加1，所以1的含义为累加1

3.常见case when格式，sum(case when 条件字段名 in (范围较小值,范围较大值) then [需要计算sum的字段名] else 0 end)

count函数中使用if判断条件格式为：count(if(条件,1,null))

1.统计总数，count(if(条件字段名=值,true,null))

2.统计总数去重复值，count(DISTINCT 需要计算count的字段名,if(条件字段名=值,true,null))

181. 超过经理收入的员工（easy）

无

窗口函数

下面几题基本都涉及到了窗口函数的知识点，涉及的窗口函数的典型应用总结如下：

分组排名：185题、184题

连续出现N次：180题

185. 部门工资前三高的所有员工（hard）

分组排名 - 窗口函数

Q：两个表。查询每个部门中收入高的员工（指一个员工的工资在该部门的不同工资中排名前三）

表Employee

id：主键

name：员工姓名

salary

departmentId：Department表中id的外键

表Department

name：部门名

窗口函数rank()、dense_rank() 和 row_number()

分析如下：

这题主要要区分rank()、dense_rank() 和 row_number() 的区别

这个题解写的贼详细：图解SQL面试题：经典TOPN问题

代码如下：

select b.name as Department,
a.name as Employee,
salary as Salary 
from (
    select *, dense_rank() over (
        partition by departmentId
        order by salary desc
    ) as ranking
    from Employee
) a, Department b 
where a.departmentId = b.id and ranking<=3

184. 部门工资最高的员工（middle）

分组排名 - 窗口函数

跟185题异曲同工，把ranking<=3（部门工资前三名）改成<=1（部门工资最高）即可

178. 分数排名（middle）

Q：表Scores有两列id（主键）和score（有两位小数点的浮点数），该表的每一行都包含了一场比赛的分数

对分数进行排序。排名按以下规则计算:

分数应按从高到低排列

如果两个分数相等，那么两个分数的排名应该相同

在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字

按 score 降序返回结果表

分析：

这题跟185题、184题都差不多，还简单点，需要注意的是对列重命名时，因为rank本身是一个函数，所以要加引号

select score, dense_rank() over(
    order by score desc 
) as 'rank' from Scores

177. 第N高的薪水（middle）

Q：表Employee有两列id（主键）和salary。查询第n高的工资，若没有第n个最高工资，则返回null

MySQL模板如下：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      
  );
END

分析如下：

这题跟上面三题也差不多，注意的是要加个distinct，否则会报错“Subquery returns more than 1 row”。比如下面这个测试用例，返回第1高的salary，是300，但是会返回两个结果，即[2, 300]，[3, 300]

代码如下：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      select distinct salary 
      from (
          select salary, 
          dense_rank() over (order by salary desc) as ranking 
          from Employee
      ) tmp where ranking=N
  );
END

180. 连续出现的数字（middle）

连续出现N次 - 窗口函数

Q：表Logs有两列：id（主键）和num，查询所有至少连续出现三次的数字

向上窗口函数lead() 和向下窗口函数lag()

分析如下：

这题的窗口函数跟上面几题不太一样

向上窗口函数lead：取出字段名所在的列，向上N行的数据，作为独立的列

lead(字段名,N,默认值) over(partion by …order by …)

向下窗口函数lag：取出字段名所在的列，向下N行的数据，作为独立的列

lag(字段名,N,默认值) over(partion by …order by …)

默认值是指，当向上N行或者向下N行值时，如果已经超出了表行和列的范围时，会将这个默认值作为函数的返回值，若没有指定默认值，则返回Null

这个题解贼详细：拼多多面试题：如何找出连续出现N次的内容？

代码如下：

select distinct Num as ConsecutiveNums
from (
    select Num,
    lag(Num,1) over (order by Id) as Num1,
    lag(Num,2) over (order by Id) as Num2
    from Logs
) tmp where tmp.Num=tmp.Num1 and tmp.Num=tmp.Num2

Cheeryang_ego

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
打赏
0
评论
【力扣-SQL】非会员剩余题刷题知识点总结

四舍五入：round(value, n)；sum()和count()中使用if判断条件：sum(if(条件,列值,0)) 和 count(if(条件,1,null)) ；窗口函数rank()、dense_rank() 和 row_number() ；向上窗口函数lead() 和向下窗口函数lag()；窗口函数的两个典型应用问题：分组排名；连续出现N次
复制链接

扫一扫