LeetCode Database 学习总结 - Section One

1、Delete Duplicate Emails

 Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.

+----+------------------+| Id | Email            |+----+------------------+| 1  | [e-mail] || 2  | [e-mail]  || 3  | [e-mail] |+----+------------------+Id is the primary key column for this table.

For example, after running your query, the above Person table should have the following rows:

+----+------------------+| Id | Email            |+----+------------------+| 1  | [e-mail] || 2  | [e-mail]  |+----+------------------+

# Write your MySQL query statement belowDELETE p1 FROM Person p1 JOIN Person p2WHERE p1.Id > p2.Id AND p1.Email = p2.Email

这里和p1是代指列么?一般删除都是DELETE FROM tableName Where Condition

2、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  |+------------+----------+--------+

思路：先根据DepartmentID分组计算出最大值,得到临时表，再与Employee表内连接，再与Deparment表内连接。

<span style="font-size:18px;"># Write your MySQL query statement belowSELECT d.Name AS Department, e.Name AS Employee, s.Salary AS SalaryFROM Employee eINNER JOIN (SELECT DepartmentId, MAX(Salary) AS Salary FROM Employee GROUP BY DepartmentId) s USING(DepartmentId, Salary)INNER JOIN Department d ON d.Id = s.DepartmentId</span>

注意：怎么用好临时表，USING后面的是什么意义？

3、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      |+----------+

# Write your MySQL query statement belowSELECT e1.Name AS EmployeeFROM Employee e1 INNER JOIN Employee e2ON e1.ManagerId = e2.IdWHERE e1.Salary > e2.Salary"""SELECT e1.Name AS EmployeeFROM Employee e1WHERE e1.Salary > (SELECT e2.Salary FROM Employee e2 WHERE e1.ManagerId = e2.Id)"""

被注释掉的代码超时了，所以SQL的效率是不是也可以考虑？

4、

Consecutive Numbers

 

<span style="font-size:18px;">SELECT DISTINCT(L1.Num)FROM Logs L1, Logs L2, Logs L3WHERE L1.Num = L2.Num AND L1.Num = L3.Num AND L1.Id + 1 = L2.Id AND L1.Id + 2 = L3.Id</span>

上面这个方法似乎过于简单和粗暴，是不是有更优雅一点的办法啊？

# Write your MySQL query statement belowSELECT DISTINCT Num FROM (  SELECT Num, COUNT(Rank) AS Cnt FROM (    SELECT    Num,              @curRank := @curRank + IF(@prevNum = Num, 0, 1) AS rank, @prevNum := Num    FROM      Logs s, (SELECT @curRank := 0) r, (SELECT @prevNum := NULL) p    ORDER BY  ID ASC  ) t GROUP BY Rank HAVING Cnt >= 3 ) n;

这段代码超时了，而且暂时还看不懂，留待以后进行修改。

5、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    |+-------+------+

这是一道自定义变量的题目

<span style="font-size:18px;">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;</span>

6、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  |+------------+----------+--------+

<span style="font-size:18px;"># Write your MySQL query statement belowSELECT D.Name AS Department, E.Name AS Employee, E.Salary AS SalaryFROM Department AS D, Employee AS EWHERE (SELECT COUNT(DISTINCT(Salary)) FROM Employee WHERE Salary > E.Salary AND E.DepartmentId = DepartmentId) < 3 AND  E.DepartmentId = D.IdORDER BY D.Id, E.Salary DESC;</span>

7、

Nth Highest Salary

 

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

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

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

<span style="font-size:18px;">CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN      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</span>

参考文献：

1、LeetCode数据库十道题解答：http://www.open-open.com/lib/view/open1422703062330.html

2、书影博客：http://bookshadow.com/weblog/2015/01/24/leetcode-department-top-three-salaries/