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;
网友评论