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 theEmployeetable.

+----+--------+ | 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 returnnull.

【解答】 題目簡單，但是如果和我一樣，發現幾個常用函數都不記得了， 可以復習一下 。

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 theEmployeetable.

+----+--------+ | 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 returnnull.

【解答】第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 aboveScorestable, 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 aboveLogstable, 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

【題目】

TheEmployeetable 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 theEmployeetable, 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 namedPerson.

+----+---------+
| 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, theCustomerstable and theOrderstable. 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

【題目】

TheEmployeetable 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            | +----+-------+--------+--------------+

TheDepartmenttable 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

【題目】

TheEmployeetable 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            | +----+-------+--------+--------------+

TheDepartmenttable 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;