Leetcode数据库专项训练

最近学完了数据库，发现数据库不是单单我以前的增删改查那么简单，也需要考虑很多方面，比如安全性，故障恢复，并发控制，数据库设计等等，每个方面都不是简单就能搞定的。

但是大多数人使用数据库时只使用到了它的数据操作语句，就是增删改查这一类语句，这确实是非常重要的一部分，也是我们在写代码过程中经常会接触到的，所以把这一部分能力强化一下也是非常有必要 的

我是个数据库的初学者，所以我也不例外，我准备将LeetCode上关于数据库的42个问题全部做一遍，并记录下来。供自己复习以及大家参考。

由于篇幅比较大，我就不放置题目描述，只给出每一题的超链接，和自己的解答，有些题目如果我自己的查询语句速度不够理想，也会放上别人更加快速的答案。

175 Combine Two Tables

select FirstName, LastName, City, State
from Person left join Address on (Person.PersonId=Address.PersonID)

176 Second Highest Salary

select max(Salary) SecondHighestSalary
from Employee
where Salary < (select max(Salary)
                from Employee);

177 Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
  RETURN (
      # Write your MySQL query statement below.
      select distinct Salary
      from Employee
      order by Salary desc limit M,1
  );
END

分析

这题我一开始不会，一是因为不会写这种嵌入式的，二是不会写~~~~

我看完答案以后主要就是不太明白为什么不加distinct关键字就是错的，想了半天后我自己认为是这样的。比如想要5th高的薪水，但是前5个薪水都是10000元，且一共就5条记录（更多记录也行，只要不一样的薪水不超过4个），这样其实就是找不到第5高的薪水了，就应该返回Null,而不加distinct就无法处理这样的结果。

知识点 -- limit用法

limit后面可以跟一个参数或者两个参数，参数必须为整数

如果参数为两个，则第一个参数的值代表第一个查询返回元祖的偏移量，数据库表的第一个元祖的偏移量为0。第二个参数的值是代表查询返回元祖的最大记录数。如果第二个参数的值为-1则返回从偏移量到表中最后一个元祖间的所有记录行。

如果参数为一个，则该参数表示返回的最大记录数。

limit n 等价于 limit 0, n

limit 10, 5 返回11-15行

178 Rank Scores

select t.score,(select count(m.score)+1 
                from  (select s.score
                       from Scores s 
                       group by score) m 
                where m.score>t.score) Rank 
from Scores t 
order by t.score desc;

分析

一共三层select from where子句，从里往外看。

m得到的是一个只有分数score字段且分数不重复出现的表

Rank是用来在查询Scores t每一条纪录(记为A)时，将m表中分数大于A.score的元组选出来，统计个数并加一。因为m表不含重复元祖，所以最终的Rank字段是连续递增的。

答案里还有种写法，我看不懂那种语法，因为没接触过，待以后有机会学习一下，直接贴在下面。

其他方法

SELECT
  Score,
  @rank := @rank + (@prev <> (@prev := Score)) Rank
FROM
  Scores,
  (SELECT @rank := 0, @prev := -1) init
ORDER BY Score desc;

180 Consecutive Numbers

select distinct t1.Num ConsecutiveNums
from Logs t1, Logs t2, Logs t3
where t1.Num = t2.Num and t1.Num = t3.Num and
      t1.Id+1 = t2.Id and t2.Id+1 = t3.Id 

英语的重要性！！！！

这题目很无语，我第一次读完题以为是选出出现三次及以上的数字。然后觉得还挺简单的，但是怎么提交都不对。。。让我错以为题目错了，然后去看了下别人的答案，然后发现别人想法跟我完全不一样，我就回去重新看了下题目，发现题目最后有个consecutively（连续的）。

好好学英语！！！

181 Employees Earning More Than Their Manages

select t1.Name Employee
from Employee t1, Employee t2
where t1.ManagerId = t2.Id and t1.Salary > t2.Salary

182 Duplicate Emails

select Email
from Person
group by Email having count(Id) >= 2;

183 Customers Who Never Order

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

184 Department Highest Salary

select t2.Name Department, t1.Name Employee, Salary
from Employee t1, Department t2,(select t3.DepartmentId, max(Salary) max_salary
                                 from Employee t3
                                 group by t3.DepartmentId) t4
where t1.Salary = t4.max_salary and t1.DepartmentId = t4.DepartmentId and
      t1.DepartmentId = t2.Id

185 Department Top Three Salaries

第一种

Select dep.Name as Department, emp.Name as Employee, emp.Salary from Department dep, 
Employee emp where emp.DepartmentId=dep.Id and 
(Select count(distinct Salary) From Employee where DepartmentId=dep.Id and Salary>emp.Salary)<3

第二种

SELECT d.Name 'Department', e1.Name 'Employee', e1.Salary
FROM Employee e1
JOIN Department d ON e1.DepartmentId = d.Id
WHERE 3 > (SELECT COUNT(DISTINCT e2.Salary)
           FROM Employee e2
           WHERE e2.Salary > e1.Salary
           AND e1.DepartmentId = e2.DepartmentId);

分析

不会。参考的别人的。讲解

sql语句写的太少，即使有了想法，也不知道哪些想法可以用语句实现，哪些不可以。

196 Delete Duplicate Emails

delete t1
from Person t1, Person t2
where t1.Email = t2.Email and t1.Id > t2.Id;

197 Rising Temperature

select t1.Id Id
from Weather t1, Weather t2
where DATEDIFF(t1.Date, t2.Date) = 1 and t1.Temperature > t2.Temperature;

分析

我的第一版错误答案如下：

select t1.ID Id
from Weather t1, Weather t2
where t1.Date = t2.Date+1 and t1.Temperature > t2.Temperature

我写出这样的错误代码也只是尝试一下，因为我不知道有DATEDIFF这样的函数。我的错误代码只有一个例子通不过，但是没有提示，自己找不出来错误点。看了答案区的评论后明白t1.Date = t2.Date+1无法处理像t2.Date='2018-1-31'这样的日期，我在交互式mysql环境下试了一下，date数据类型的2018-1-31加1后变为字符串型的20180132。

262 Trips and Users

SELECT Request_at as Day,
       ROUND(COUNT(IF(Status != 'completed', TRUE, NULL)) / COUNT(*), 2) AS 'Cancellation Rate'
FROM Trips
WHERE (Request_at BETWEEN '2013-10-01' AND '2013-10-03')
      AND Client_id NOT IN (SELECT Users_Id FROM Users WHERE Banned = 'Yes')
GROUP BY Request_at;

分析

这题我又没做出来。。。我不知道ROUND这个函数，也不知道COUNT还能这样用。。（怎么有这么多函数，数据库白学了吗？？上网搜了下，网上数据库教程里确实有，学校选的教材里没有-。-！）但我的想法跟这个sql语句是一样的。

ROUND()函数把一个字段的数值舍入为固定位数的数值。接受两个参数，第一个为需要转换的字段名，第二个为小数位数。

595 Big Countries

select name, population, area
from World
where population > 25000000 or area > 3000000;

596 Classes more than 5 students

select class
from courses
group by class having count(distinct student) >= 5

注意

count里需要加上distinct，我一开始想的是应该没有重复的，但是提交失败了，发现表中有重复元祖，想想也是，如果数据库表建立的时候没强制要求，是可以重复。

601 Human Traffic of Stadium

select distinct t1.*
from stadium t1, stadium t2, stadium t3
where t1.people>99 and t2.people>99 and t3.people>99
      and (
          (t1.id+1=t2.id and t2.id+1=t3.id)
           or (t1.id-1=t2.id and t1.id+1=t3.id)
           or (t2.id+1=t3.id and t3.id+1=t1.id)
          )
order by t1.id;

620 Not Boring Movies

select id, movie, description, rating
from cinema
where id%2=1 and description!='boring'
order by rating desc;

626 Exchange Seats

update seat t1, seat t2
set t1.student = t2.student, t2.student=t1.student
where t1.id%2=1 and t2.id=t1.id+1;

select * from seat;

627 Swap Salary

UPDATE salary
SET
    sex = CASE sex
        WHEN 'm' THEN 'f'
        ELSE 'm'
    END;

分析

这题我没做出来，我知道会有这样的选择判断语法，但我不知道怎么写，所以直接看答案了～～～

总结

到这里为止，Leetcode上免费的DATABASE题目已经做完了，总的来说呢，难的题目看半天也很难写出来，简单的题目一眼就知道怎么写了，还需要多做一些练习，虽然还是有题目做不出来，但是肯定是会有进步的，也会发现不足。

我目前的不足就是不知道SQL的一些函数，我学的教材可能比较基础吧，只介绍了SELECT这些语句以及数据库其他方面的知识：数据库设计，安全，并发等等。还需要寻找其他教程、书籍。