select FirstName,LastName,City,State
from Person
leftjoin Address using(PersonId)
176. 第二高的薪水
select(selectdistinct salary
from Employee
orderby salary desclimit1,1)as SecondHighestSalary
177. 第N高的薪水
CREATEFUNCTION getNthHighestSalary(N INT)RETURNSINTBEGINRETURN(# Write your MySQL query statement below.select(selectdistinct salary
from(select
salary
,dense_rank()over(orderby salary desc)as rk
from Employee
) t
where rk=n
));END
178. 分数排名
# Write your MySQL query statement belowselect
score
,dense_rank()over(orderby score desc)as`rank`from Scores
orderby`rank`asc
180 连续出现的数字
selectdistinct num as ConsecutiveNums
from(select
num
,id-cast(dense_rank()over(partitionby num orderby id asc)as signed)as rn
from logs) t1
groupby rn,num
havingcount(*)>=3
181 超过经理收入的员工
select e1.name as Employee
from Employee e1
leftjoin Employee e2 on e1.managerId =e2.id
where e1.salary>e2.salary
182 查找重复的电子邮箱
select Email
from Person
groupby Email
havingcount(*)>=2
183 从不订购的客户
select Name as Customers
from Customers c
leftjoin Orders o on c.id=o.CustomerId
where CustomerId isnull
184 部门工资最高的员工
select Department,Employee,Salary
from(select
d.name as Department
,e.name as Employee
,salary as Salary
,dense_rank()over(partitionby departmentId orderby salary desc)as rk
from Employee e
leftjoin Department d on e.departmentId =d.id
) t
where rk=1
185 部门工资前三高的所有员工
select Department,Employee,Salary
from(select
d.name as Department
,e.name as Employee
,salary as Salary
,dense_rank()over(partitionby departmentId orderby salary desc)as rk
from Employee e
leftjoin Department d on e.departmentId =d.id
) t
where rk in(1,2,3)
196 删除重复的电子邮箱
delete u
from Person u , Person v
where v.id < u.id and u.email = v.email
197 上升的温度
select id
from(select
id
,recordDate
,Temperature
,date_add(lag(recordDate,1)over(orderby recordDate ),interval1day)aslast,lag(Temperature,1)over(orderby recordDate )as tomorrow
from Weather
) t
where Temperature>tomorrow and datediff(last,recordDate)=0
262 行程和用户
select
request_at asDay,round(avg(casewhenstatus='completed'then0else1end),2)as"Cancellation Rate"from Trips t
leftjoin Users u1 on t.client_id = u1.users_id
leftjoin Users u2 on t.driver_id = u2.users_id
where(request_at between"2013-10-01"and"2013-10-03")and u1.banned ='No'and u2.banned ='No'groupby request_at
orderby request_at
595 大的国家
select name,population,area
from World
where area>=3000000or population>=25000000
596 超过5名学生的课
select class
from Courses
groupby class
havingcount(1)>=5
601 体育馆的人流量
select id,visit_date,people
from(select id,visit_date,people,count(*)over(partitionby rk)as rn
from(select id,visit_date,people,cast(row_number()over(orderby visit_date)as signed)-id as rk
from Stadium
where people>=100) a
) b
where rn>=3orderby visit_date
620 有趣的电影
select*from cinema
where id%2=1and description !='boring'orderby rating desc
626 换座位
selectcasewhen id%2=1and id =(selectcount(*)from Seat)then id
when id%2=1then id+1else id-1endas id,student
from Seat
orderby id asc;