4.从不订购的客户

需求：某网站包含两个表,Customers表和Orders表，编写SQL查询，找出所有从不订购商品的客户。

展示效果：

建表语句：

Create table If Not Exists Customers (Id int, Name varchar(255));
Create table If Not Exists Orders (Id int, CustomerId int);
Truncate table Customers;
insert into Customers (Id, Name) values (1, ‘Joe‘);
insert into Customers (Id, Name) values (2, ‘Henry‘);
insert into Customers (Id, Name) values (3, ‘Sam‘);
insert into Customers (Id, Name) values (4, ‘Max‘);
Truncate table Orders;
insert into Orders (Id, CustomerId) values (1, 3);
insert into Orders (Id, CustomerId) values (2, 1);

方法1:

select 
	c.name as `customer`
from Customers
where c.id not in (select customerid from Orders);

方法2：

select 
	c.name as `customer`
from Customers c
left join 
	Orders o 
on c.id = o.Customerid 
where o.id is null ;  --  或者 custmerid is null 

5.部门工资最高的员工

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

效果展示：

建表语句：

Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int);
Create table If Not Exists Department (Id int, Name varchar(255));
Truncate table Employee;
insert into Employee (Id, Name, Salary, DepartmentId) values (1, ‘Joe‘, 75000, 1);
insert into Employee (Id, Name, Salary, DepartmentId) values (2, ‘Jim‘, 90000, 1);
insert into Employee (Id, Name, Salary, DepartmentId) values (3, ‘Henry‘, 80000, 2);
insert into Employee (Id, Name, Salary, DepartmentId) values (4, ‘Sam‘, 60000, 2);
insert into Employee (Id, Name, Salary, DepartmentId) values (5, ‘Max‘, 90000, 1);
insert into Employee (Id, Name, Salary, DepartmentId) values (6, ‘Randy‘, 85000, 1);
insert into Employee (Id, Name, Salary, DepartmentId) values (7, ‘Will‘, 70000, 1);
Truncate table Department;
insert into Department (Id, Name) values (1, ‘IT‘);
insert into Department (Id, Name) values (2, ‘Sales‘);

方法1：

select 
	d.name as ‘department‘,
	e.name as ‘employee‘,
	Salary
from 
	Employee as e 
join 
	Department d
on
	e.departmentid = d.id 
where 
	(e.department,Salary ) in 
	(select 
     	department ,
     	max(salary)
     from department 
     group by departmentid 
    );

方法2：

select 
	department ,employee, salary
from (
	select 
    	d.name as ‘department‘,
    	e.name as ‘employee‘,
    	e.salary,
    	rank() over(partition by d.id order by salary desc ) rk 
    from
    	employee e
    join 
    	department d
    on e.demartmentid = d.id
) t1
where rk = 1;

需求2：找出每个部门获得前三高工资的所有员工。

展示效果：

方法1：

select 
	d.name as ‘department‘,
	e.name as ‘employee‘,
	e1.salary
from
	employee e1
join 
	department d
on
	e1.departmentid = d.id
where 
	(select 
    	distinct count(e2.salary)
    from 
    	employee e2 
    where 
    	e1.salary < e2.salary
    and 
    	e1.deparmentid = e2.departmentid) < 3
order by department,e1.salary desc;    	

方法2：

select 
	department,
	emplyee,
	salary
from 
	(
        select
        	d.name as ‘department‘,
        	e.name as ‘employee‘,
        	e.salary,
        	dense_rank() over(partition by d.id order by salary desc ) rk
        from 
        	employee e
        join  
        	department d
        on
        	e.departmentid = d.id 
    )t1
where rk <= 3;

6.超过经理收入的员工

需求：Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。

效果展示：

建表语句：

create table If Not Exists Employee (Id int, Name varchar(255), Salary int, ManagerId int);
truncate table Employee;
insert into Employee (Id, Name, Salary, ManagerId) values (1, ‘Joe‘, 70000, 3);
insert into Employee (Id, Name, Salary, ManagerId) values (2, ‘Henry‘, 80000, 4);
insert into Employee (Id, Name, Salary, ManagerId) values (3, ‘Sam‘, 60000, null);
insert into Employee (Id, Name, Salary, ManagerId) values (4, ‘Max‘, 90000, null);

方法：

select
	e1.name as ‘employee‘
from 
	employee e1
join 
	emplyee e2
on 
	e1.managerid = e2.id 
where e1.salary > e2.salary

SQL -3