我想在每个部门选择EmployeeName，Department和Max Salary

本文介绍了我想在每个部门选择EmployeeName，Department和Max Salary的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一张桌子员工。我想在每个部门选择姓名，部门和最高工资。请帮助。

I have a table Employee. I want to select Name,Department and Max salary in each department. Please help.

EmpID	 Name   Department	Salary
 1	         A	   IT	     5000
 2	         B	   IT	     4000
 3	         C	   QA	     4000
 4	         D	   SS	     8000
 5	         E	   IT	     10000
 6	         F	   QA	     4500

推荐答案

SELECT
    Employee.*
FROM Employee
INNER JOIN (
    SELECT
        Department,
        MAX(Salary) AS TopSalary
    FROM Employee
    GROUP BY
        Department) AS DepartmentSalaries
    ON Employee.Department = DepartmentSalaries.Department
    AND Employee.Salary = DepartmentSalaries.TopSalary

SELECT EmpID, Name, Department, Salary
FROM (
   SELECT EmpID, Name, Department, Salary, ROW_NUMBER() OVER(Partition By Department ORDER BY Salary DESC) AS RowNo
   FROM Employee
) AS T
WHERE RowNo=1

这篇关于我想在每个部门选择EmployeeName，Department和Max Salary的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！