在SQL刷题的过程中经常会遇到这类问题-关于分组比较的。在这里,自己经常使用2种方法第一个子查询,第二个则是窗口函数(这是真的香!)
例子:题目来自leetcode上面的一道原题
有两张表:分别是Employee表和Department表
(1)Employee表
(2)Department表
要求得到:现在让你找出每个部门工资最高的员工及其部门名称和薪水
方法一:常规
(1)首先很明显我们先要在Employee表里面找到每个部门的最高工资-这里就要根据 DepartmentId分类
select DepartmentId,max(Salary)
from Employee
group by DepartmentId
(2)就很简单了,把Employee和Department表连接起来,再用where 语句让 员工的部门号和工资在上面查那个表里面就行了
select Department.name as Department,Employee.Name as Employee,Salary from Employee
inner join Department
on Employee.DepartmentId=Department.id
where (Employee.DepartmentId,Salary) in
(select DepartmentId,max(Salary)
from Employee
group by DepartmentId)
得到了题目要求的结果
方法二:利用窗口函数(用起来是真的简单)
这道题我用的是dense_rank()over(),关于row_number() over(), rank()over()区别,这里不多说
DENSE_RANK() over(PARTITION by DepartmentId ORDER BY Salary desc)
注释:PARTITION by DepartmentId ORDER BY Salary desc 意思是根据DepartmentId 分类,把部门salary进行降序排列
select Id,Name,Salary,DepartmentId, DENSE_RANK()over(PARTITION by DepartmentId ORDER BY Salary desc ) as '排名' from employee
运行结果如图:直接得到了每个员工的工资在部门中的排名
剩下的就只要连表就好了,并且取排名=1就好了
SELECT t.Name,t.Employeename,t.Salary from
(select department.Name,employee.Name as Employeename,employee.Salary,DENSE_RANK() over(PARTITION by DepartmentId ORDER BY Salary desc) as '排名' from employee
left join department on department.Id=employee.DepartmentId) as t
where t.排名=1
有的人肯定要说,我就要第一种方法,不想用窗口函数,当然可以。但是
如果题目中要让你取各个部门工资排第3,或者工资排名<=5?
你如果用第一种,应该比较负责。但是如果你用窗口函数,只需要把最后排名的条件改一下,就可以使用了。例:t.排名=2 或 t.排名<=5.就好了。
这不香嘛,舒服的很
看到这里的大佬们,可以动动小手,点个赞。大夏天的,热的很。