sql刷题

262. Trips and Users 难题

case when 记得加end， between如果不是数字的话记得加引号

答案：为了把users里面的client和driver分离 用了 两个表

select Request_at as 'Day',

    round(sum(case when Status like 'cancelled%' then 1 else 0 end)/count(Status),2) as 'Cancellation Rate'

    from Trips ,Users as Users_client, Users as Users_driver

    where Users_client.Banned='No'

    and Users_client.Role='client'

    and Users_client.Users_Id=Trips.Client_Id

    and Users_driver.Banned='No'

    and Users_driver.Role='driver'

    and Users_driver.Users_id=Trips.Driver_id

    and Request_at between '2013-10-01' and '2013-10-03'

    group by Request_at

185. Department Top Three Salaries 难题

这是一个分组 组内排序 top n的问题

https://blog.csdn.net/wguangliang/article/details/50167283这个文章说的挺好的

select D.Name as Department, E.Name as Employee, Salary from Employee as E join Department as D on E.DepartmentId=D.Id where 3>(select count(distinct(Salary)) from Employee where Employee.DepartmentId=E.DepartmentId and Employee.Salary>E.Salary) order by Department, Salary desc

SELECT d.Name as Department, e1.Name as Employee, e1.Salary FROM Department d, Employee e1, Employee e2 WHERE d.ID = e1.DepartmentId and e1.DepartmentId = e2.DepartmentId and e1.Salary <= e2.Salary group by d.ID,e1.Name having count(distinct e2.Salary) <= 3 order by d.Name, e1.Salary desc

601. Human Traffic of Stadium 难题

select * from stadium where id in (select s1.id from stadium s1, stadium s2, stadium s3 where s1.id=s2.id+1 and s2.id=s3.id+1 and s1.people>=100 and s2.people>=100 and s3.people>=100 union select s2.id from stadium s1, stadium s2, stadium s3 where s1.id=s2.id+1 and s2.id=s3.id+1 and s1.people>=100 and s2.people>=100 and s3.people>=100 union select s3.id from stadium s1, stadium s2, stadium s3 where s1.id=s2.id+1 and s2.id=s3.id+1 and s1.people>=100 and s2.people>=100 and s3.people>=100)

626. Exchange Seats 用了coalesce 幂函数用power 中等题

select s1.id, coalesce(s2.student,s1.student) as student from seat s1 left join seat s2 on s2.id=s1.id-power(-1,s1.id%2) order by s1.id