leetcode 数据库：sql知识记录

1、排名问题
涉及到排名，就使用窗口函数

select 
rank() over (order by 成绩 desc) as 'rank',
dense_rank() over (order by 成绩 desc) as 'dense_rank',
row_number() over (order by 成绩 desc) as 'row_number'
from Scores

2、sql执行过程

3、连续出现的数字（180）

select distinct Num as ConsecutiveNums
from    
 (  	select Num,        
	if(@pre=Num,@count:=@count+1,@count:=1) as nums,
	@pre:=Num #@是局部变量，：=是赋值，=是判断
        from Logs l,        
        (select @pre:=null,@count:=1) as pc    
 ) as n
where nums>=3

4、部门工资前三高（185）

select d.Name as Department,e.Name as Employee,Salary
from  Employee e, Department d 
where e.DepartmentId=d.Id 
and 3>
(    	select count(distinct e2.Salary)     
	from Employee e2    
	where e.Salary<e2.Salary    
	and e.DepartmentId=e2.DepartmentId
	)

5、体育馆的人流量（601）
至少连续三行中记录不小于100，

with t1 as(
select id, visit_date,people,
id-rank() over(order by id) as rk 
from stadium
where people>99 )

select id, visit_date,people
from t1
where rk in (   
select rk from t1 group by rk having count(rk)>2
)

6.行程和用户（262）

select t.Request_at as 'Day',
round(
sum(if(t.Status='completed',0,1))
/
count(t.Status),
2
) as 'Cancellation Rate'
from Trips as t
join Users as u1 on (t.Client_Id=u1.Users_Id and u1.Banned='no')
join Users as u2 on (t.Driver_id=u2.Users_Id and u2.Banned='no')
where t.Request_at between '2013-10-01' and '2013-10-03'
group by t.Request_at