题目

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


Department 表包含公司所有部门的信息。


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

分析

-- 查看员工表
SELECT
	* 
FROM
	employee;


-- 查看部门表
SELECT
	* 
FROM
	department;


-- 每个部门的最大薪水
SELECT
	DepartmentId,
	MAX( Salary ) 
FROM
	Employee 
GROUP BY
	DepartmentId;

![在这里插入图片描述](https://img-blog.csdnimg.cn/20200225113606400.png)

-- 连接员工表和部门表
SELECT
	* 
FROM
	Employee
	JOIN department ON Employee.DepartmentId = Department.Id;


-- 加上前面那个部门薪水最高的表过滤一下
SELECT
	* 
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Employee.Salary) IN
    (   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 , Employee.Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)
;

解答

-- 查看员工表
SELECT
	* 
FROM
	employee;
	
-- 查看部门表
SELECT
	* 
FROM
	department;
	
-- 每个部门的最大薪水
SELECT
	DepartmentId,
	MAX( Salary ) 
FROM
	Employee 
GROUP BY
	DepartmentId;
	
-- 连接员工表和部门表
SELECT
	* 
FROM
	Employee
	JOIN department ON Employee.DepartmentId = Department.Id;

-- 加上前面那个部门薪水最高的表过滤一下
SELECT
	* 
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Employee.Salary) IN
    (   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 , Employee.Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)
;

相关企业

半年内

半年~1年

1年~2年