[SQL]力扣题目练习记录

2021年2月18日 09:24:09
176.第二高薪水

select ifnull(
( select distinct salary 
from employee 
order by salary desc
limit 1,1 ),null)
as SecondHighestSalary 

知识点：1.order by desc 降序排序。
2. limit 1,1 = limit 1 offset 1 查询前1条，过滤1条。(注：limit m,1 == limit 1 offset m )。
3. ifnull 用来查询不为空的数据，为空设置为某个值。

178.分数排名

select a.Score as Score ,
 (select count(distinct b.Score) from Scores b where b.Score >= a.Score) as 'Rank'
 from Scores a 
order by a.Score desc 

知识点：
思路是这道题的关键：计算出比当前分数大于等于的分数数目就是当前分数的rank分。

180.连续出现的数字

select 
distinct a.num as ConsecutiveNums
from 
logs a,
logs b,
logs c
where 
a.id = b.id -1
and b.id =c.id -1 
and a.num = b.num 
and b.num = c.num 

from 后跟多个表，表示求笛卡尔积。
法二：窗口函数lead

https://blog.csdn.net/qq_41081716/article/details/108865011?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522161379016916780269850169%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=161379016916780269850169&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allsobaiduend~default-3-108865011.pc_search_result_no_baidu_js&utm_term=lead%E7%AA%97%E5%8F%A3%E5%87%BD%E6%95%B0

select 
distinct f.num as ConsecutiveNums 
from 
( select num,lead(num,1) over() as num1,lead(num,2) over() as num2
  from logs 
) f
where f.num = f.num1 and f.num1 = f.num2

lead(row,offset,default) 从下往上进行滑动
row 代表目标列名，offset 表示移动的行数，default 为移动后空出位置默认填值。

185.部门工资前三高

select d.name as department,a.name as Employee, a.Salary 
from Employee a 
JOIN Department d on a.departmentid = d.id
where 3 > (
select count(distinct b.salary) 
from Employee b 
where a.Salary < b.Salary 
and a.DepartmentId = b.DePARTMENTId)

思路：求前几高 都可以用COUNT distinct 来进行过滤筛选。
之后进行映射操作选择需要展示的列即可。

196.删除重复的电子邮箱

delete a 
from Person a ,Person b 
where a.Email = b.Email and a.id > b.id

解释：删除表a中，满足WHERE条件的数据行。