数据蛙数据分析每周作业

leetcode SQL刷题 难题记录

2019-05-10  本文已影响3人  晓迦

181.超过经理的员工

题目分析:Employee表中包含员工的Id、工资、以及该员工的经理的Id,要找出员工工资大于他经理的员工名字。
解题思路1:将原表复制一次,然后左连接,这样员工的工资和他经理的工资就在同一张表中(自关联),然后用where执行条件:员工的工资大于他经理的工资。

select e1.Name as Employee from Employee as e1 left join Employee as e2 on e1.ManagerId = e2.Id where e1.Salary > e2.Salary

解题思路2:使用子查询,先找出该员工经理的工资,然后再做比较

select e1.Name as Employee from Employee as e1 where e1.Salary > (select Salary from Employee where Id = e1.ManagerId)

183.从不订购的客户

题目分析:题目中包含2个表,Customers表和Orders表,要找出在没有订购的客户。
解题思路1:left join + where

select Name as Customers from Customers left join Orders on Customers.Id = Orders.CustomerId where Orders.Id is null

解题思路2:子查询

select Name as Customers from Customers where Id not in (select CustomerId from Orders)

PS:183题与181题思路类似。

626.换座位

题目分析:将相邻俩个学生的座位调换,通过改变学生的id号来实现座位的调换。
解题思路:将奇数号的学生位号+1,偶数号的学生位号-1,同时如果最后一个学生位号为奇数,则不改变它的位号。

select (case
 when id%2 = 1 and id = (select count(*) from seat) then id
 when id%2 = 1 then id+1
 else id-1
 end) as id,student 
 from seat
 order by id;

178.分数排行

题目分析:对分数进行排行
解题思路1:使用窗口函数dense_rank

select Score,
dense_rank() over (order by Score desc) as Rank
from Scores
order by Score desc;

窗口函数参考博客
解题思路2:复制一个表,计算原表中分数小于等于复制表分数的不重复个数,即可作为Rank值。

select s.Score,(select count( distinct(Score) ) from Scores where Score >= s.score) as Rank
from Scores s order by Score desc

体育馆的人流量

解题思路1:自联结三个表,分为三种情况排序。

select distinct s1.* from stadium s1,stadium s2,stadium s3 where s1.people>=100 and s2.people>=100 and s3.people>=100 and 
((s1.id=s2.id-1 and s1.id=s3.id-2) or
 (s1.id=s2.id+1 and s1.id=s3.id-1) or 
(s1.id=s2.id+1 and s1.id=s3.id+2))
 order by s1.id asc;

解题思路2:三个表联结,找出连续3天人流量大于等于的100的id,然后查找id

SELECT DISTINCT s4.id,s4.date,s4.people
 FROM stadium s1,stadium s2,stadium s3,stadium s4 
WHERE s1.id+1=s2.id 
AND s2.id+1=s3.id 
AND s1.people>=100 AND s2.people>=100 AND s3.people>=100
 AND s4.id IN (s1.id,s2.id,s3.id);

177.第N高的薪水

解题思路:排序 limit offset的使用
(LIMIT 4 OFFSET 3 指的是从第3行开始起的4行数据 换一种写法:LIMIT 3,4)
先按从大到小排序,然后从N-1个数据起的第一个数据即为第N高的数据。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET N
  );
END

262.行程和用户

解题思路1:
Trips与Users表左联结根据Role不同左联结两次,然后找出Banned为Yes的用户,在这张大表中计算取消率。

select tri.Request_at as Day , (round(count(if(tri.status != 'completed', tri.status, null)) / count(tri.status), 2)) as 'Cancellation Rate'  
from (select t.Status,t.Request_at 
      from Trips t left join Users u1 on t.Client_Id = u1.Users_Id and u1.Role = 'client'  
      left join Users u2 on t.Driver_Id = u2.Users_Id and u2.Role = 'driver' 
      where u1.Banned = 'No' and u2.Banned = 'No' 
      and t.Request_at >= '2013-10-01' and t.Request_at <= '2013-10-03') tri
group by tri.Request_at

ps:if(tri.status != 'completed', tri.status, null) 意思是将status列中不是‘completed’的值保留,其他变为null,方便计数。
解题思路2:我好像写麻烦了,看看大佬的解法.

select 
t.Request_at as Day, 
ROUND(count(t.Status <> 'completed' or null) / count(1), 2)  as 'Cancellation Rate'
from Trips t
inner join Users cli on cli.Users_Id = t.Client_Id and cli.Role = 'client' and cli.Banned = 'No'
inner join Users dri on dri.Users_Id = t.Driver_Id and dri.Role = 'driver' and dri.Banned = 'No'
where t.Request_at >= '2013-10-01'
and t.Request_at <= '2013-10-03'
group by t.Request_at

185.部门工资前三高的员工

解题思路:工资前三高指的是工资数在你前面的人数少于3人。

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

参考:Lykit01的文章,leedcode数据库解题(一)leedcode数据库解题(二)

上一篇下一篇

猜你喜欢

热点阅读