SQL练习笔记一：部门员工最高工资

用户6184845

发布于 2021-01-04 12:50:33

7700

发布于 2021-01-04 12:50:33

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

-- 创建Employee表 --
CREATE TABLE Employee(
  Id int(4) not null,
  Name VARCHAR(10) not null,
  Salary int(10) not null,
  DepartmentId int(4) not NULL
);

-- 增加4条数据 --
INSERT INTO Employee VALUES(1,'Joe',70000,1);
INSERT INTO Employee VALUES(2,'Henry',80000,2);
INSERT INTO Employee VALUES(3,'Sam',60000,2);
INSERT INTO Employee VALUES(4,'Max',90000,1);

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

-- 创建Department表 --
CREATE TABLE Department(
  Id int(4) not null,
  Name VARCHAR(10)
);

-- 增加2条数据 --
INSERT into Department VALUES(1,'IT');
INSERT into Department VALUES(2,'Sales');

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

-- 通过排序（必须加上LIMIT，不然结果不正确）与GROUP BY结合求出部门内最高工资，INNER JOIN再关联上部门名 --
SELECT
  D.Name AS Department,
  E.Name AS Employee,
  Salary 
FROM
  ( SELECT * FROM Employee ORDER BY Salary DESC LIMIT 10 ) AS E
  INNER JOIN Department AS D ON E.DepartmentId = D.Id 
GROUP BY
  E.DepartmentId;

问题2：查询部门工资前3的信息。

-- 增加2条数据 --
INSERT INTO Employee VALUES(5,'Janet',69000,1);
INSERT INTO Employee VALUES(6,'Randy',85000,1);

-- 使用窗口函数对各部门的员工工资进行分组排序
SELECT
  ( SELECT D.Name FROM Department AS D WHERE DepartmentId = D.Id ) AS 'Department',
  Name,
  Salary 
FROM
  ( SELECT Name, Salary, DepartmentId, RANK() over ( PARTITION BY DepartmentId ORDER BY Salary DESC ) AS ranking FROM Employee ) AS b 
WHERE
  ranking <= 3;