LeetCode练习 -- SQL

1. 编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于下面两表提供 person 的以下信息：
   FirstName, LastName, City, State

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

2. 第二高的薪水（176.）
   编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。
   

# Write your MySQL query statement below
SELECT
    IFNULL(
        (SELECT DISTINCT Salary
        FROM Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL)AS SecondHighestSalary;

3. （177.）
   

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    DECLARE P INT DEFAULT N-1; --设置变量P
    RETURN (
    # Write your MySQL query statement below.
        SELECT
            IFNULL(
            (SELECT DISTINCT Salary
            FROM Employee
            ORDER BY Salary DESC
            LIMIT P,1),
        NULL)
  );
END

4. 超过经理收入的员工(181.)
   

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

5. 查找重复的电子邮箱(182. )
   

# Write your MySQL query statement below
SELECT DISTINCT a.Email AS Email
FROM Person AS a,Person AS b
WHERE a.Email = b.Email AND a.Id != b.Id;

6. 从不订购的客户(183. )
   
   

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

7. 部门工资最高的员工(184.)
   
   

# Write your MySQL query statement below
SELECT Department.name AS 'Department',Employee.name AS 'Employee',Salary
FROM Employee JOIN Department ON Employee.DepartmentId = Department.Id
WHERE (Employee.DepartmentId,Salary) IN
(
    SELECT DepartmentId,MAX(Salary)
    FROM Employee
    GROUP BY DepartmentId
);

Ps：果然对于现在很菜的我来说，稍微有点难度，还是有点难以把持啊~~~~~
没有关系，再接再励鸭~
Next one – — –

8. 删除重复的电子邮箱（196.）
   

# Write your MySQL query statement below
DELETE FROM Person
WHERE Id NOT IN (   -- 删除不在查询结果中的值
    SELECT Id FROM
   (
       SELECT MIN(Id) AS Id -- 排除Email相同时中Id较大的行
       FROM Person
       GROUP BY Email
   ) AS temp    -- 此处需使用临时表，否则会发生报错
)

代码参考自 https://leetcode-cn.com/problems/delete-duplicate-emails/solution/delete-zi-cha-xun-shi-ce-xiao-lu-gao-yu-guan-fang-/