【LeetCode】SQL语句题解（一）

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

示例：

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+
根据以上输入，你的查询应返回以下结果：

+---------+
| Email   |
+---------+
| a@b.com |
+---------+
说明：所有电子邮箱都是小写字母。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/duplicate-emails
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

182. 查找重复的电子邮箱：

SELECT DISTINCT p1.Email FROM Person p1
JOIN Person p2 ON p1.Email = p2.Email
WHERE p1.Id <> p2.Id;
SELECT Email FROM Person GROUP BY Email
HAVING COUNT(*) > 1;

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Orders 表：

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
例如给定上述表格，你的查询应返回：

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/customers-who-never-order
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

183. 从不订购的客户:

select a.Name as Customers from Customers a left join Orders b on a.Id=b.CustomerId where b.CustomerId is null;
SELECT Name AS Customers FROM Customers 
WHERE Id NOT IN (SELECT CustomerId FROM Orders);

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/department-highest-salary
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

184. 部门工资最高的员工:

select b.Name as Department,e1.Name as Employee,e1.Salary from Employee e1,Department b 
where e1.DepartmentId=b.Id and e1.Salary =(select max(e2.Salary) from Employee e2 where e2.DepartmentId=b.Id );

select b.Name as Department,e1.Name as Employee,e1.Salary from Employee e1,Department b 
where e1.DepartmentId=b.Id and e1.Salary >=all(select e2.Salary from Employee e2 where e2.DepartmentId=b.Id );

SELECT d.Name AS Department, e1.Name AS Employee, e1.Salary FROM Employee e1
JOIN Department d ON e1.DepartmentId = d.Id WHERE Salary IN 
(SELECT MAX(Salary) FROM Employee e2 WHERE e1.DepartmentId = e2.DepartmentId);

//最快方法，groupby的用法
select b.Name as Department,a.Name as Employee,a.Salary as Salary from Employee a inner join Department b on a.DepartmentId = b.Id 
where (a.Salary,a.DepartmentId) in (select max(Salary),DepartmentId from Employee group by DepartmentId);

185. 部门工资前三高的员工 :

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 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            |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+
编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/department-top-three-salaries
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

题解：难题，可以先左连接到employee表中（包含了所有数据）然后连接条件除了部门id相同还要筛选员工id根据子查询是从员工表中找到前三高的员工可以用having count技巧，先对employee表按照部门id自连接，然后根据员工id（自连接会产生多个）分组，找出那些id对应的salary在所在id组中是前三高的即可；除了筛选员工id还要对部门id排序吧同一个部门放一起然后工资递减

# Write your MySQL query statement below
select d.Name as Department, e.Name as Employee,e.Salary 
from Employee e left join Department d
on e.DepartmentId = d.Id 
where e.Id in
(
    select e1.Id
    from Employee e1 left join Employee e2
    on e1.DepartmentId=e2.DepartmentId and e1.Salary<e2.Salary
    group by e1.Id
    having count(distinct e2.Salary)<=2
    
)and e.DepartmentId in (select Id from Department) order by d.Id,e.Salary desc;


SELECT d.Name AS Department, e.Name AS Employee, e.Salary FROM Employee e
JOIN Department d on e.DepartmentId = d.Id
WHERE (SELECT COUNT(DISTINCT Salary) FROM Employee WHERE Salary > e.Salary
AND DepartmentId = d.Id) < 3 ORDER BY d.Id, e.Salary DESC;