【力扣-SQL】非会员剩余题 刷题知识点总结
创始人
2024-06-03 07:56:22
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 
casewhen id%2=1 and 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

  • id

  • 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 departmentIdorder by salary desc) as rankingfrom 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
BEGINRETURN (# 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
BEGINRETURN (# 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 Num2from Logs
) tmp where tmp.Num=tmp.Num1 and tmp.Num=tmp.Num2

相关内容

热门资讯

东方航空×泡泡玛特:DIMOO... 文旅观察近日,中国东方航空与泡泡玛特携手推出的DIMOO中泰建交50周年限定主题彩绘专机正式首航,执...
多个“第一”!重大突破! 今天(7月9日)上午国新办举行首场“高质量完成‘十四五’规划”系列主题新闻发布会一起看 ↓国家发展改...
中广核新能源:6月发电量140... 格隆汇7月9日|中广核新能源(1811.HK)公告,6月本集团按合并报表口径完成发电量1,403.5...
1.52亿千瓦!江苏电网最高用... 转自:上观新闻7月7日,江苏电网最高用电负荷,年内第三次刷新历史新高,达1.52亿千瓦。截至目前,南...
ST华铭因信披违规被罚,收购聚... 近日,ST华铭(维权)(300462)发布公告,近日收到中国证券监督管理委员会上海监管局下发的《行政...
山西泽辰医药乌帕替尼缓释片启动... 药物临床试验登记与信息公示平台数据显示,山西泽辰医药科技有限公司的乌帕替尼缓释片在健康受试者中随机、...
西海都市报公布地址、订阅及零售... 本报地址:西宁市长江路5号 广告经营许可证: 广告部电话:6125601 排版:西海都市报社编辑部 ...
台风丹娜丝“绘就”陆家嘴诗意画... 转自:上观新闻连日来,受台风“丹娜丝”外围环流持续影响,上海晴雨相间,同时带来降温、大风,云雾光影间...
国家发改委:中国做的这件事,全... 转自:北京日报客户端在国新办今天(9日)举行的首场“高质量完成‘十四五’规划”系列主题新闻发布会上,...
或受欧盟法规影响,苹果将在欧盟...   炒股就看金麒麟分析师研报,权威,专业,及时,全面,助您挖掘潜力主题机会! IT之家 7 月 9...