每日sql-部门最高工资

两个表，一张Employee一张Department

建表语句：

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));insert into Employee (Id, Name, Salary, DepartmentId) values (1, 'Joe', 70000, 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);insert into Department (Id, Name) values (1, 'IT');insert into Department (Id, Name) values (2, 'Sales');

解决思路
一、
先用Employee join Department 获得目标字段
然后where 限定Employee.DepartmentId , Salary在一个临时表里
临时表通过分组求 部门最高工资。

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	);

二、
先找出Employee所有字段，
然后join内关联临时表，临时表分组求部门最高的工资
然后关联Department求部门名字

select
c. name as Department,
a.name as Employee,
b.Salary
from Employee a 
join 
( 
SELECT      DepartmentId ,MAX(Salary) as   Salary    
FROM   Employee        
GROUP BY DepartmentId
) b 
on a.DepartmentId=b.DepartmentId and a.Salary=b.Salary 
join  Department c 
on a.DepartmentId = c.Id;

求每个部门前3工资的人

利用第二种方法，把max去掉，改成order by desc 然后 取limit 3即可

 select c. name as Department, a.name as Employee, b.Salary from Employee a  join  (  SELECT      DepartmentId ,Salary as   Salary     FROM   Employee         GROUP BY DepartmentId ,salary order by Salary desc limit 3) b  on a.DepartmentId=b.DepartmentId and a.Salary=b.Salary  join  Department c  on a.DepartmentId = c.Id;