LeetCode Database 学习总结 - Section One

最新推荐文章于 2024-08-10 10:32:33 发布

AlywinXee

最新推荐文章于 2024-08-10 10:32:33 发布

阅读量2.8k

点赞数

本文链接：https://blog.csdn.net/AlywinXee/article/details/46340975

版权

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  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
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  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

# Write your MySQL query statement below
DELETE p1 FROM 
Person p1 JOIN Person p2
WHERE 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 below
SELECT d.Name AS Department, e.Name AS Employee, s.Salary AS Salary
FROM Employee e
INNER 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 below
SELECT e1.Name AS Employee
FROM Employee e1 INNER JOIN Employee e2
ON e1.ManagerId = e2.Id
WHERE e1.Salary > e2.Salary

"""
SELECT e1.Name AS Employee
FROM Employee e1
WHERE 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 L3
WHERE 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 below
SELECT 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 below
SELECT D.Name AS Department, E.Name AS Employee, E.Salary AS Salary
FROM Department AS D, Employee AS E
WHERE (SELECT COUNT(DISTINCT(Salary)) FROM Employee WHERE Salary > E.Salary AND E.DepartmentId = DepartmentId) < 3 AND  E.DepartmentId = D.Id
ORDER 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 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</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/