SQL 各部门最高工资
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8477040/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Highest Salary in each department
提问by Qinnovator
I have a table EmpDetails:
我有一张桌子EmpDetails：
DeptID EmpName Salary
Engg Sam 1000
Engg Smith 2000
HR Denis 1500
HR Danny 3000
IT David 2000
IT John 3000
I need to make a query that find the highest salary for each department.
我需要查询每个部门的最高工资。
回答by AVee
As short as the question:
就像问题一样简短：
SELECT DeptID, MAX(Salary) FROM EmpDetails GROUP BY DeptID
回答by Joe Stefanelli
Assuming SQL Server 2005+
假设 SQL Server 2005+
WITH cteRowNum AS (
SELECT DeptID, EmpName, Salary,
DENSE_RANK() OVER(PARTITION BY DeptID ORDER BY Salary DESC) AS RowNum
FROM EmpDetails
)
SELECT DeptID, EmpName, Salary
FROM cteRowNum
WHERE RowNum = 1;
回答by Prem Ranjan Jha
SELECT empName,empDept,EmpSalary
FROM Employee
WHERE empSalary IN
(SELECT max(empSalary) AS salary
From Employee
GROUP BY EmpDept)
回答by Dinesh
Select empname,empid,Sal,DeptName from
(Select e.empname,e.empid,Max(S.Salary) Sal,D.DeptName, ROW_NUMBER() Over(partition by D.DeptName order by s.salary desc) Rownum
from emp e inner join Sal S
on e.empid=s.empid
inner join Dept d on e.Deptid=d.Deptid
group by e.empname,e.empid,D.DeptName,s.Salary
) x where Rownum = 1
回答by Ankit Sharma
If you want to show other parameters too along with DeptIdand Salarylike EmpName, EmpId
如果你也想显示其他参数DeptId和Salary喜欢EmpName，EmpId
SELECT
EmpID
, Name,
, Salary
, DeptId
FROM Employee
where
(DeptId,Salary)
in
(select DeptId, max(salary) from Employee group by DeptId)
回答by naveen
SELECT
DeptID,
Salary
FROM
EmpDetails
GROUP BY
DeptID
ORDER BY
Salary desc
回答by Saiprasad
Use the below quesry:
使用以下查询：
select employee_name,salary,department_id from emp where salary in(select max(salary) from emp group by department_id);
回答by Dwipam Katariya
select deptid, empname, salary from
(Select deptid, empname,salary,
rank() Over(Partition by deptid order by salary desc)as rank from
EmpDetails) emp
where emp.rank = 1
First ranks each employee by salary in descending order having highest rank 1 and then selects only deptid, empname, salary. You can do this for all Nth member of the group.
首先按最高排名 1 的薪水降序排列每个员工，然后仅选择 deptid、empname、salary。您可以为组中的所有第 N 个成员执行此操作。
回答by PankajN
***
> /*highest salary by each dept*/
***
select d.Dept_Name,max(e.salary)
from emp_details as e join Dept_Details as d
on e.d_id=d.Dept_Id
group by d.Dept_Name
select distinct e.d_id,d.Dept_Name
from emp_details as e join Dept_Details as d
on e.d_id=d.Dept_Id
select e.salary,d.Dept_Name,d.Dept_Id
from emp_details as e join Dept_Details as d
on e.d_id=d.Dept_Id
/////simplest query for max salary dept_wise////
回答by vijayinani
This will work if the department, salary and employee name are in the same table.
如果部门、薪水和员工姓名在同一个表中，这将起作用。
select ed.emp_name, ed.salary, ed.dept from
(select max(salary) maxSal, dept from emp_dept group by dept) maxsaldept
inner join emp_dept ed
on ed.dept = maxsaldept.dept and ed.salary = maxsaldept.maxSal
Is there any better solution than this?
还有比这更好的解决方案吗？
