表: Employee
+--------------+---------+ | Column Name | Type | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +--------------+---------+ id 是该表的主键列(具有唯一值的列)。 departmentId 是 Department 表中 ID 的外键(reference 列)。 该表的每一行都表示员工的ID、姓名和工资。它还包含了他们部门的ID。
表: Department
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ id 是该表的主键列(具有唯一值的列)。 该表的每一行表示部门ID和部门名。
创建数据库:
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', '85000', '1') insert into Employee (id, name, salary, departmentId) values ('2', 'Henry', '80000', '2') insert into Employee (id, name, salary, departmentId) values ('3', 'Sam', '60000', '2') insert into Employee (id, name, salary, departmentId) values ('4', 'Max', '90000', '1') insert into Employee (id, name, salary, departmentId) values ('5', 'Janet', '69000', '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')
要求:
公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。
编写解决方案,找出每个部门中 收入高的员工 。
分析:
首先,对于这道题来说,我们通过题意可以得到一定要分组-‘每个部门’所有我们要对部门进行分组,分组分为group by和开窗函数。又因为他要‘排名前三(其中可能会有并列情况)’,所以我们要选择开窗函数可以满足这些需求。对部门进行排名对薪资进行排名排序号,然后并列情况,可以使用dense_rank()函数排序的同时还可以对相同的薪资排相同的序号。
select *,
dense_rank() over (partition by departmentId order by salary desc ) xxx
from employee
通过以上函数我们可以得到每个部门的排名。
然后我们对排序进行筛选就可以得到每个部门的前三名。这时候我们需要用到临时表 。
with t1 as 把上面的表当作一个临时表,然后我们进行筛选出排名小于等于3的人。
with t1 as (select *,
dense_rank() over (partition by departmentId order by salary desc ) xxx
from employee)
select t1.name Employee,
salary
from t1
where xxx <= 3
通过上面描述书写代码 ,我们可以得到每个部门工资前三的人。
题目要求我们返回部门的名称,员工名字,还有薪资。因为部门的名称在另一个表中,我们需要对这个临时表和部门表进行关联,然后取出部门的名称。然后我们就可以得到最终的代码。
with t1 as (select *,
dense_rank() over (partition by departmentId order by salary desc ) xxx
from employee)
select department.name Department,
t1.name Employee,
salary
from t1
join department on t1.departmentId = department.id
where xxx <= 3