LeetCode数据库十道题解答

在之前做算法题的途中发现，LeetCode上面推出了数据库解答，有十道题，于是这两天晚上时间就给做了。解答是次要主要的好处是，正好复习复习SQL一些查询语句的写法，比如自定义变量和常用函数。题目都比较简单，少做解释，以贴题目和答案为主。

Combine Two Tables

【题目】

Table: Person

+-------------+---------+ | Column Name | Type    | +-------------+---------+ | PersonId    | int     | | FirstName   | varchar | | LastName    | varchar | +-------------+---------+ PersonId is the primary key column for this table.

Table: Address

+-------------+---------+ | Column Name | Type    | +-------------+---------+ | AddressId   | int     | | PersonId    | int     | | City        | varchar | | State       | varchar | +-------------+---------+ AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

FirstName, LastName, City, State

【解答】

select p.FirstName, p.LastName, a.City, a.State from Person p left outer join Address a on p.PersonId=a.PersonId;

Second Highest Salary

【题目】

Write a SQL query to get the second highest salary from the Employee table.

+----+--------+ | Id | Salary | +----+--------+ | 1  | 100    | | 2  | 200    | | 3  | 300    | +----+--------+

For example, given the above Employee table, the second highest salary is . If there is no second highest salary, then the query should return null .

【解答】 题目简单，但是如果和我一样，发现几个常用函数都不记得了， 可以复习一下 。

select IFNULL( (select e.Salary from Employee e group by e.Salary order by e.Salary desc limit 1, 1), NULL) SecondHighestSalary;

Nth Highest Salary

【题目】

Write a SQL query to get the n ^th highest salary from the Employee table.

+----+--------+ | Id | Salary | +----+--------+ | 1  | 100    | | 2  | 200    | | 3  | 300    | +----+--------+

For example, given the above Employee table, the n ^th highest salary where n = 2 is . If there is no n ^th highest salary, then the query should return null .

【解答】第n高，这就得用自定义变量了，平时很少用这东西， 于是复习了一下先 。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN  RETURN (   select IFNULL(Salary, NULL) Salary from (    select @row_num := @row_num+1 Rank, Salary from (     select Salary from Employee group by Salary desc    ) t1 join (     select @row_num := 0 from dual    ) t2   ) t where t.Rank=N  ); END 

Rank Scores

【题目】

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no “holes” between ranks.

+----+-------+ | Id | Score | +----+-------+ | 1  | 3.50  | | 2  | 3.65  | | 3  | 4.00  | | 4  | 3.85  | | 5  | 4.00  | | 6  | 3.65  | +----+-------+

For example, given the above Scores table, your query should generate the following report (order by highest score):

+-------+------+ | Score | Rank | +-------+------+ | 4.00  | 1    | | 4.00  | 1    | | 3.85  | 2    | | 3.65  | 3    | | 3.65  | 3    | | 3.50  | 4    | +-------+------+

【解答】又是一道自定义变量的题目，这种模式应当熟悉，还是挺常见的。如果不能使用“set @var_name=0;”的话（要求用一句SQL搞定），那可以在子句里面定义“select @var_name:=0”，再在它的外面使用这个变量。

select s.Score, t.Rank from (     select @row_num:=@row_num+1 Rank, Score from (          select Score from Scores group by Score desc     ) t1 join (         select @row_num := 0 from dual     ) t2 ) t, Scores s where s.Score=t.Score group by Score desc, Rank asc, Id;

Consecutive Numbers

【题目】

Write a SQL query to find all numbers that appear at least three times consecutively.

+----+-----+ | Id | Num | +----+-----+ | 1  |  1  | | 2  |  1  | | 3  |  1  | | 4  |  2  | | 5  |  1  | | 6  |  2  | | 7  |  2  | +----+-----+

For example, given the above Logs table, is the only number that appears consecutively for at least three times.

【解答】

select DISTINCT(l1.Num) from Logs l1, Logs l2, Logs l3 where l1.Id+1=l2.Id and l1.Id+2=l3.Id and l1.Num=l2.Num and l1.Num=l3.Num;

Employees Earning More Than Their Managers

【题目】

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

+----+-------+--------+-----------+ | Id | Name  | Salary | ManagerId | +----+-------+--------+-----------+ | 1  | Joe   | 70000  | 3         | | 2  | Henry | 80000  | 4         | | 3  | Sam   | 60000  | NULL      | | 4  | Max   | 90000  | NULL      | +----+-------+--------+-----------+

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

+----------+ | Employee | +----------+ | Joe      | +----------+

【解答】

select e.Name from Employee e, Employee m where e.ManagerId=m.Id and e.Salary>m.Salary;

Duplicate Emails

【题目】

Write a SQL query to find all duplicate emails in a table named Person .

+----+---------+ | Id | Email   | +----+---------+ | 1  | a@b.com | | 2  | c@d.com | | 3  | a@b.com | +----+---------+

For example, your query should return the following for the above table:

+---------+ | Email   | +---------+ | a@b.com | +---------+

Note : All emails are in lowercase.

【解答】

select distinct(p.Email) from Person p, Person q where p.Id!=q.Id and p.Email=q.Email;

Customers Who Never Order

【题目】

Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything.

Table: Customers .

+----+-------+ | Id | Name  | +----+-------+ | 1  | Joe   | | 2  | Henry | | 3  | Sam   | | 4  | Max   | +----+-------+

Table: Orders .

+----+------------+ | Id | CustomerId | +----+------------+ | 1  | 3          | | 2  | 1          | +----+------------+

Using the above tables as example, return the following:

+-----------+ | Customers | +-----------+ | Henry     | | Max       | +-----------+

【解答】

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

Department Highest Salary

【题目】

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+ | Id | Name  | Salary | DepartmentId | +----+-------+--------+--------------+ | 1  | Joe   | 70000  | 1            | | 2  | Henry | 80000  | 2            | | 3  | Sam   | 60000  | 2            | | 4  | Max   | 90000  | 1            | +----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+ | Id | Name     | +----+----------+ | 1  | IT       | | 2  | Sales    | +----+----------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT         | Max      | 90000  | | Sales      | Henry    | 80000  | +------------+----------+--------+

【解答】

select d.Name Department, e.Name Employee, s.Salary from (     select MAX(e.Salary) Salary, e.DepartmentId from Employee e, Department d where e.DepartmentId=d.Id group by e.DepartmentId ) s, Employee e, Department d where s.Salary=e.Salary and e.DepartmentId=d.Id and e.DepartmentId=s.DepartmentId;

Department Top Three Salaries

【题目】

The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.

+----+-------+--------+--------------+ | Id | Name  | Salary | DepartmentId | +----+-------+--------+--------------+ | 1  | Joe   | 70000  | 1            | | 2  | Henry | 80000  | 2            | | 3  | Sam   | 60000  | 2            | | 4  | Max   | 90000  | 1            | | 5  | Janet | 69000  | 1            | | 6  | Randy | 85000  | 1            | +----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+ | Id | Name     | +----+----------+ | 1  | IT       | | 2  | Sales    | +----+----------+

Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows.

+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT         | Max      | 90000  | | IT         | Randy    | 85000  | | IT         | Joe      | 70000  | | Sales      | Henry    | 80000  | | Sales      | Sam      | 60000  | +------------+----------+--------+

【解答】这道题拿MySql做其实是有点难了，如果我用Oracle，我就可以 rank() over(partition by xxx order by xxx desc) 这样搞了，但是MySql比较挫的地方在于没有这样分区操作的东西。不过最后还是借助了三个自定义变量搞定。意思就不解释了，还是好懂的：

select Department, Employee, Salary from (  select   IF(@lastDep!=t1.Department, @count:=0, @count:=@count), IF(@lastDep=t1.Department and @lastSalary!=t1.Salary, @count:=@count+1, @count:=@count) Cnt,   @lastDep:=t1.Department, @lastSalary:=t1.Salary,   t1.Department, t1.Employee, t1.Salary  from (   select d.Name Department, e.Name Employee, e.Salary   from Department d, Employee e where d.Id=e.DepartmentId order by Department asc, Salary desc  ) t1, (    select @lastDep:=null, @lastSalary:=0, @count:=0 from dual  ) t2 ) f where Cnt<3; 

事后，我去看了看讨论区，发现一个漂亮的解答，没有用任何自定义变量，关键就是distinct(Salary)去和原Salary比较，过滤掉这个条件下出现次数大于3的情况：

select D.Name as Department, E.Name as Employee, E.Salary as Salary    from Employee E, Department D    where (select count(distinct(Salary)) from Employee             where DepartmentId = E.DepartmentId and Salary > E.Salary) in (0, 1, 2)          and             E.DepartmentId = D.Id           order by E.DepartmentId, E.Salary DESC;