【力扣-SQL】非会员剩余题 刷题知识点总结
之前刷完了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<=3184. 部门工资最高的员工（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 Scores177. 第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);
END180. 连续出现的数字（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