表: 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和部门名。
公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。
编写解决方案,找出每个部门中 收入高的员工 。
以 任意顺序 返回结果表。
返回结果格式如下所示。
示例 1:
输入: Employee 表: +----+-------+--------+--------------+ | id | name | salary | departmentId | +----+-------+--------+--------------+ | 1 | Joe | 85000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | | 6 | Randy | 85000 | 1 | | 7 | Will | 70000 | 1 | +----+-------+--------+--------------+ Department 表: +----+-------+ | id | name | +----+-------+ | 1 | IT | | 2 | Sales | +----+-------+ 输出: +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Joe | 85000 | | IT | Randy | 85000 | | IT | Will | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 | +------------+----------+--------+
实现代码:
select dn as Department,
en as Employee,
salary as Salary
from(
select d.name as dn,
e.name as en,
e.salary,
dense_rank() over (partition by departmentid order by salary desc) as d_rank
from Employee e left join Department d on e.departmentid = d.id
) as t_ed
where d_rank <= 3;
注意点:
1.这是原代码,执行错误:
select dn as Department,
en as Employee,
salary as Salary,
from(
select d.name as dn,
e.name as en,
e.salary,
dense_rank() over (partition by departmentid order by salary desc) as d_rank
from Employee e left join Department d on e.departmentid = d.id
) as t_ed
where d_rank <= 3;
想了半天不得其解,原来是from前有个逗号,导致报错:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from( select d.name as dn, e.name as en, e.salary, ' at line 5
2.派生表一定要有别名
如果没有给子查询表别名t_ed,会出现如下报错:
Every derived table must have its own alias. 所有派生表必须拥有自己的别名。
3.窗口函数的使用
dense_rank() over (partition by departmentid order by salary desc)
第一部分:选取函数 dense_rank()
第二部分:over( )
第三部分:partition by 分类
第四部分:order by 排序 窗口函数内,同样可以加DESC