文章目录
176.第二高的薪水,没有返回null
ifnull(x,y),若x不为空则返回x,否则返回y,这道题y=null
limit x,y,找到对应的记录就停止
distinct,过滤关键字
select
ifnull
(
(select distinct(salary )
from employee
order by salary desc
limit 1,1),
null
) as SecondHighestSalary
180.连续出现的数字
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
select distinct(t1.num) as ConsecutiveNums
from logs as t1
inner join logs as t2 on t1.id+1=t2.id and t1.num=t2.num
inner join logs as t3 on t2.id+1=t3.id and t2.num=t3.num
开窗函数
SELECT DISTINCT Num as ConsecutiveNums
FROM(
SELECT Id,Num,
LAG(Num,1)OVER(ORDER BY Id) as last_1,
LAG(Num,2)OVER(ORDER BY Id) as last_2
FROM Logs
) as consecutivenum
WHERE consecutivenum.Num = consecutivenum.last_1
AND consecutivenum.last_1 = consecutivenum.last_2
196 删除重复的电子邮箱
编写一个 SQL 查询,来删除 Person 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。
delete p1
from person p1 join person p2
on p1.email=p2.email and p1.id>p2.id
197. 上升的温度
日期函数
给定一个 Weather 表,编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 Id。
select w2.id
from weather w1 ,weather w2
where w2.Temperature>w1.Temperature and datediff(w2. RecordDate,w1. RecordDate)=1
262. 行程和用户
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表,你的 SQL 语句应返回如下结果,取消率(Cancellation Rat