leetcode数据库题解（二）
#180连续出现的数字
这题还是用自关联的形式，即l1，l2，l3，并且给出Id连续限制和Num相等的限制。
select l1.Num as ConsecutiveNums
from Logs l1,Logs l2,Logs l3
where (l1.Id=l2.Id-1 and l1.Num=l2.Num and l1.Id=l3.Id-2 and l1.Num=l3.Num) order by l1.Num asc;
这么写很复杂，并且扩展性不高，当题目改成连续的4个或者5个时，重新写很麻烦。有大神给出了类似procedure的版本：
SELECT distinct num as ConsecutiveNums FROM(
SELECT id, num,
@pre := @cur,
@cur := num,
@rep_ct := IF(@pre = @cur, @rep_ct + 1, 1) as rep_ct
FROM `Logs` l, (SELECT @pre := null, @cur := 0, @rep_ct := 1) init
) temp WHERE rep_ct >= 3;
就是计算在自己之前出现了多少个和自己相等的连续的数。
#197上升的温度
这题也不难，用自关联就可以了。不过要注意，日期可以直接运算（如下），也可以用函数to_days(t1)-to_days(t2)=1或者DATEDIFF(t1,t2)=1。
select w2.Id from Weather w1,Weather w2 where w2.RecordDate=w1.RecordDate+1 and w2.Temperature>w1.Temperature order by w2.Id;
#601体育馆的人流量
注意这题和连续出现的数字有点不一样，后者只需要管到第3个数字就可以了并且只需要输出第一个数字，因此对比时只以l1为基础。这题则要把所有连续的条目都输出出来，因此要考虑连续的3天的头、中、尾的情况。注意要加上distinct，因为对于一个三天来说，可能date在尾部，但是对于另一个三天来说就在头部了，因此会重复计数。
select distinct s1.id,s1.date,s1.people 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;
有网友这样的解答也挺好的，把s1,s2,s3的id都储存起来，而不是只取s1。
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高的薪水
这题已经给好函数的框架了，只需要写中间的查询语句就可以了。
思路是把不重复的Salary按降序排列，然后取第N个Salary就好了，用limit 1 offset N-1，但是offset后面不能传入表达式（亲测不行），因此要在前面把N先减1。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N=N-1;
RETURN (
# Write your MySQL query statement below.
select Salary from (select distinct Salary from Employee order by Salary desc)tmp limit 1 offset N
);
END
还有一种思路，就是用自关联算出有多少个不重复的Salary大于当前Salary，如果等于N-1，就选出。这种思路的计算量大，要慢的多。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N=N-1;
RETURN (
# Write your MySQL query statement below.
select distinct Salary from Employee where N-1=(select count(distinct Salary) from Employee e2 where e2.Salary> Employee.Salary)
);
END
#262行程和用户
题目要求的是2013-10-01到2013-10-03的非禁止用户的取消率，所以要先把非禁止的用户选出来，禁止用户可能是client，也可能是driver，只要其中有一个是禁止用户，就不要。然后要用sum和count函数算取消率。注意要用round函数取小数的位数。
select tmp.Request_at as 'Day',round(sum(tmp.Status!='complete')/count(tmp.Status),2) as 'Cancellation Rate' from ( select Status,Request_at from Trips,Users where ((Client_Id=Users_Id and Banned='No') or (Driver_Id=Users_Id and Banned='No') ) and Request_at between '2013-10-01' and '2013-10-03' )tmp group by tmp.Request_at order by tmp.Request asc;
sum函数里也可以这么写sum(case when t.Status like "cancelled%" then 1 else 0 end)
#596超过5名学生的课
这题也不难，注意题目的提示：学生在每个课中不应被重复计算。因此我们要先用distinct筛选一次。另外注意用having时前面一定要用group by。
select t.class from (select distinct sudents,class from courses)t group by t.class having count(t.class)>=5;
也可以写的更简洁一点：
select class from courses group by class having count(distinct student)>=5;
#184部门工资最高的员工
这题不难，用条件查询和max函数就行了。
select d.Name as Department
,e.Name as Employee,e.Salary as Salary
from Employee e,Department d where e.DepartmentId=d.Id and e.Salary=(select max(e2.Salary) from Employee e2 where e2.DepartmentId=d.Id);
判断条件也可以用别的语句写，来看看最快的一个答案：
select d.Name as Department, e.Name as Employee, e.Salary
from Employee as e
join Department as d on e.DepartmentId = d.Id
where (e.DepartmentId, e.Salary) IN (
select DepartmentId, max(Salary)
from Employee
group by DepartmentId
);
#185部门工资前三高的员工
这题和上一题很像，改一下后面的条件就可以了。要寻找在这个人前面的人少于3个，即前三(select count(distinct Salary) from Employee where Employee.Salary>e.Salary and Employee.DepartmentId=e.DepartmentId)<3。这题要注意可以有并列名次的，也就是说如果第一名有多个，都要选出来，也就是说要看它前面有多少个不重复的Salary
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 e2.Salary>e.Salary)<3 order by d.Name,e.Salary desc;
#176第二高的薪水
这题和第N高的薪水很类似。就是把n改为2。我们沿用之前的limit 1 offset 1的写法。
select t.Salary as 'SecondHighestSalary' from (select distinct Salary from Employee)t limit 1 offset 1;
怎么写不会通过，因为题目要求不存在第二高的薪水的话要返回null，而我们这么写会返回空。因为此时只有1个元素，limit 1 offset 1之后就只有空了。
我们要改为这样：
select (select distinct Salary from Employee order by Salary desc limit 1 offset 1) as 'SecondHighestSalary';
当不存在第二高的薪水的话，(select distinct Salary from Employee order by Salary desc limit 1 offset 1) 返回的为空，从空里面再select的话就会选出NULL。
用下面的写法可以输出NULL，因为max的对象为空，选出来的就是NULL。这个写法的思路是从去除最高的薪水的表中再选出最高的薪水。
select max(Salary) as 'SecondHighestSalary' from Employee where Salary <(select max(Salary) from Employee);
终于写完了！大家有更好的解法欢迎告诉我~祝大家刷题愉快！
