LeetCode——MySQL

一、查找重复邮箱（182）

美团笔试

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

Id	Email
1	2@baidu.com
2	1@baidu.com
3	2@baidu.com

select Email 
from Person 
group by Email 
having count(Email)>1;

二、查询从不订购的顾客（183）

customers表：id name
orders表： id Customersid

select customers.name as 'Customers'
from customers
where customers.id not in
(
    select customerid from orders
);

三、部门工资最高的员工（184）

题解

首先，第一个表可以分组查出最大工资

SELECT
    DepartmentId, MAX(Salary)
FROM
    Employee
GROUP BY DepartmentId;

最终：

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
	)