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

点击上方蓝字关注「网优小兵玩Python」

◆ ◆ ◆

创建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;