MySQL习题集

格式：

题号+题名+简单思路+code

LeetCode T627: 交换工资

• CASE语句的用法

# Write your MySQL query statement below
UPDATE salary SET
sex=CASE sex
WHEN "m" THEN "f"
WHEN "f" THEN "m"
END;

LeetCode T175: 组合两个表

• 外连接

# Write your MySQL query statement below
SELECT Person.FirstName, Person.LastName, Address.City, Address.State
FROM Person LEFT OUTER JOIN Address
ON Person.PersonId=Address.PersonId;

LeetCode T176: 第二高的薪水

• LIMIT&OFFSET用法

SELECT
(SELECT DISTINCT Salary FROM Employee
ORDER BY Salary DESC LIMIT 1 OFFSET 1)
AS SecondHighestSalary;

• MAX函数，注意MAX(NULL)=NULL

SELECT MAX(Salary) AS SecondHighestSalary 
FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee);

拓展：第N大的数

LeetCode T177: 第N高的薪水

• 自定义函数

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 1 OFFSET M 
  );
END

LeetCode T178: 分数排名

• 使用别名区分不同表

# Write your MySQL query statement below
SELECT a.Score AS Score, 
(SELECT COUNT(DISTINCT b.score) FROM Scores AS b WHERE b.score >= a.score) AS `Rank`
FROM Scores AS a ORDER BY a.Score DESC;

LeetCode T180: 连续出现的数字

• 使用交叉连接

# Write your MySQL query statement below
SELECT DISTINCT L1.Num AS ConsecutiveNums
FROM Logs L1, Logs L2, Logs L3
WHERE L1.Id+1=L2.Id AND L2.Id+1=L3.Id
AND L1.Num=L2.Num AND L2.Num=L3.Num;

LeetCode T181: 超过经理收入的员工

• 别名+子查询的使用

# Write your MySQL query statement below
SELECT a.Name AS Employee
FROM Employee AS a
WHERE a.Salary > (SELECT b.Salary FROM Employee AS b WHERE b.Id=a.ManagerId);

LeetCode T182: 查找重复的电子邮箱

• 使用GROUP BY+HAVING过滤重复值

# Write your MySQL query statement below
SELECT Email FROM Person
GROUP BY Email HAVING COUNT(Email)>1;

• 使用内连接来过滤重复值

# Write your MySQL query statement below
SELECT DISTINCT p1.Email
FROM Person AS p1 INNER JOIN Person AS p2
ON p1.Email = p2.Email AND p1.Id != p2.Id;

LeetCode T183: 从不订购的客户

• 在A不在B
• MySQL的in语句和or哪个执行效率更高?

# Write your MySQL query statement below
SELECT Name AS Customers 
FROM Customers
WHERE Id NOT IN (SELECT CustomerId FROM Orders);

LeetCode T184: 部门工资最高的员工

• 多表内连接
• 输出的信息分布在多个表中

# Write your MySQL query statement below
SELECT
t3.Name AS Department,
t2.Name AS Employee,
t1.M_s AS Salary
FROM (SELECT DepartmentId AS D_id, MAX(Salary) AS M_s 
FROM Employee GROUP BY DepartmentId) AS t1
INNER JOIN Employee AS t2
ON t1.M_s=t2.Salary AND t1.D_id=t2.DepartmentId
INNER JOIN Department AS t3
ON t1.D_id=t3.Id

LeetCode T185: 部门工资前三高的员工

• 用COUNT解决求出前三高的表
• 再连接Department表

# Write your MySQL query statement below
SELECT
t2.Name AS Department,
t1.Name AS Employee,
t1.Salary AS Salary
FROM (SELECT Name, DepartmentId, a.Salary FROM Employee AS a
WHERE (SELECT COUNT(DISTINCT b.Salary) FROM Employee AS b 
WHERE b.DepartmentId=a.DepartmentId AND b.Salary>a.Salary)<3) AS t1
INNER JOIN Department AS t2
ON t1.DepartmentId=t2.Id;

LeetCode T196: 删除重复的电子邮箱

• 使用内连接找到应该删除的Id
• 注意当同时进行删除和查询同一张表时需要进行拷贝

# Write your MySQL query statement below
DELETE FROM Person
WHERE Id in 
(SELECT a.Id
FROM (SELECT * FROM Person) AS a
INNER JOIN (SELECT * FROM Person) AS b
ON a.Email=b.Email AND a.Id>b.Id);

• 使用分组查询找到应该删除的Id

# Write your MySQL query statement below
DELETE FROM Person
WHERE Id NOT IN
(SELECT MIN(Id) FROM (SELECT * FROM Person) AS tmp GROUP BY Email HAVING COUNT(Email)>=1)